Django Raw Query: Making Count query with group BY clause

Question

For some reason I have to use raw SQL query which would be passed to django to execute with mymodel.objects.raw(query). But I see that primary key needs to be passed always. This is what stopping me in making some queries.

Imagine a simple query where I do a count(*) on a table along with conditional check on two columns:

select count(*), column_value from tableX where column_label = 'Age' group by column_value having column_value > 30;

This would work just fine in pgsql and give results like:

 count | column_value 
-------+----------------
     1 |       38.00000
     2 |       45.00000
     1 |       35.00000
     1 |       44.00000

Note the second row. Thats exactly I would want the result to be. But with django I have to pass primary key along and for that I have to change the query like:

Considering 'id' as the primary key:

    select count(*), id, column_value from tableX where column_label = 'Age' group by column_value, id having column_value > 30;

Now this will give me something like this:

 count |  id  | column_value 
-------+------+----------------
     1 | 4041 |       45.00000
     1 | 3876 |       38.00000
     1 | 3821 |       45.00000
     1 | 3931 |       35.00000
     1 | 3986 |       44.00000
(5 rows)

This is of no use to me if even after running aggregation commands I get to see all individual rows. Is there any other way to get the first result mentioned here with RAW query only with django? Some way to hack around the primary key?

Answer 1

One possible solution would be to use connection.cursor() directly and execute the raw query:

from django.db import connection

cursor = connection.cursor()
cursor.execute("""select 
                      count(*), column_value 
                  from 
                      tableX 
                  where 
                      column_label = 'Age' 
                  group by 
                      column_value 
                  having 
                      column_value > 30""")
result = cursor.fetchall()