Re: Performing raw sql queries in database

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

Le jeudi 25 juin 2020 à 16:55:54 UTC+2, tejasj...@gmail.com a écrit :

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 ??

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/abd9bab6-29d0-4687-bb0e-194bf21eb490n%40googlegroups.com.