Performing raw sql queries in database

[tejas joshi]

Hi,

I want to return the first occurrence of repetitive fields in my model.

I have a title field in my model as follows :

models.py 

class Post(models.Model):

title = models.CharField(max_length=20)

There will be multiple 'Post' with the same title.

I want to exectute the following query on the Post model

Post.objects.raw("SELECT title,ROW_NUMBER() OVER(PARTITION BY title ORDER BY title) AS row_num FROM basement_post;")

I am getting a syntax error "("

Is django not compatible with SQL server queries or am I missing something ??

[Cbh Bnh]

Hi,

Perhaps this could help you:

I had to find how many versions of each "edge" object without using a db version column

So basically for this kind of dataset:

'''
| ID      | edge_id      | geom      |
+====+=========+=======+
| 1       | 22                  | yyyyy       |
| 2       | 22                 | xxxxx        |
| 3       | 11                  | aaaaa      |
'''

I expected a queryset like

'''

    [{'id':1, 'edge_id':22, 'geom':'yyyyy', 'counter': 2}, {'id':2, 'edge_id':22, 'geom':'xxxxx', 'counter': 2}, {'id':3, 'edge_id':11, 'geom':'aaaaaa', 'counter': 1}]

'''

So I used this kind of django request:

'''

        from django.db.models import Count, IntegerField, OuterRef, Subquery

        counted_edges = Edge.objects.values('edge_id').filter(
            edge_id=OuterRef('edge_id'),
        ).annotate(
            counter=Count('edge_id')
        ).order_by()

        annotated_edges = Edge.objects.all().annotate(
                pg_counter=(Subquery(
                    counted_edges.values('counter'), output_field=IntegerField()
                )),
         )

'''

So if I needed only the instances where my 'counter' is greater then 1 I think it will be done like this:

'''

        # .......

        annotated_edges.filter(pg_counter__gt=1)

'''

I don't know if there is any better solution with raw SQL but hope this can help