How to include a column for search, but exclude it from the GROUP BY group?

Question

I am trying to execute the following query using Django ORM:

SELECT object_repr, content_type_id, COUNT(object_repr) FROM django_admin_log GROUP BY object_repr ORDER BY COUNT(object_repr); 

The closest I got:

 LogEntry.objects.values('object_repr', 'content_type__id')\ .annotate(num_logs=Count('object_repr'))\ .order_by('num_logs') 

Unfortunately, this gives an SQL statement that returns an incorrect (or at least unexpected) result:

 SELECT `django_admin_log`.`object_repr`, `django_admin_log`.`content_type_id`, COUNT(`django_admin_log`.`object_repr`) AS `num_logs` FROM `django_admin_log` GROUP BY `django_admin_log`.`object_repr`, `django_admin_log`.`content_type_id` ORDER BY num_logs 

The values ​​() method includes all of the specified columns in the GROUP BY segment, and I do not want the content_type_id in my GROUP BY group. Is it possible to do this in ORM?

edit: It turns out this is not possible in ORM. However, in the interest of future people who find this question, a request that I wrote that does what I need:

 SELECT DISTINCT djl2.id, djl1.object_id, djl1.object_repr, djl1.content_type_id, COUNT(djl1.content_type_id) AS num_items FROM django_admin_log AS djl1 INNER JOIN django_admin_log AS djl2 ON djl1.id=djl2.id GROUP BY djl1.object_repr, djl1.content_type_id ORDER BY num_items 

Mostly self-join. Good luck and hope this helps.

Answer 1

I actually just went through this in the last few days and posted a question, but, in my opinion, it is not as well worded as yours.

In any case, I found that although Sqlite and MySql will not give an error when starting the query, when you select the columns not found in the group by clause, these results may be somewhat questionable, since the standard for sql does not seem to be allow it. I know that MSSQL will return an error when you try to do this, I'm not sure about the other benefits of sql.

Based on this, I came to the conclusion that django does not allow generating such a query through ORM, because it is technically invalid, although it can work to some extent on some SQL features.

Here is the link to my question for reference: Can I control GROUP BY in django 1.3 spelling?