Using annotate for group by in django orm

Usage

The previous ORM group by method can no longer be used in Django 1.8. It needs to be implemented using annotate

Example 1

The first values ​​is used to select the field that needs to be used for group by (here group by user_id ), followed by annotate to group and aggregate the required fields (the number of question_id corresponding to each user_id and the minimum value of catalog_id are required), and then values ​​to actually query the required fields (aliases of the original user_id and the aggregated fields)

The first values ​​are used to specify the fields used for group by, which must be aggregate functions such as Count and Min (for example, using F ("user_id") to take aliases will not work). If there is no need for final query, there is no need to aggregate.

The second values ​​is used to specify the actual selected field. Only the field name after annotation can be specified (take this as an example: user_id is a field used for grouping and can be taken directly, while other fields must be aggregated and aggregated using After the alias, qid and cid, if the original table also has a field status, this field is not aggregated in annotate, so the final value cannot be queried for this field)

q = PxbNCEUserQuest.objects.filter(user_id=335).values("user_id").annotate(qid=Min("question_id"), cid=Min("catalog_id")).values("user_id", "qid", "cid")
print q
print q.query

# 输出
[{'qid': 22, 'user_id': 335L, 'cid': 17}]

SELECT `pxb_nce_user_quest`.`user_id`, MIN(`pxb_nce_user_quest`.`question_id`) AS `qid`, MIN(`pxb_nce_user_quest`.`catalog_id`) AS `cid` FROM `pxb_nce_user_quest` WHERE `pxb_nce_user_quest`.`user_id` = 335 GROUP BY `pxb_nce_user_quest`.`user_id` ORDER BY NULL

Example 2

The same as example 1, but the first one here annotate is used to group fields, and the second annotate is used to alias other fields individually

q = PxbNCEUserQuest.objects.filter(user_id=335).values("user_id").annotate(qid=Min("question_id"), cid=Min("catalog_id")).annotate(uid=F("user_id")).values("uid", "qid", "cid")
print q
print q.query
# 输出：
[{'qid': 22, 'uid': 335L, 'cid': 17}]

SELECT MIN(`pxb_nce_user_quest`.`question_id`) AS `qid`, MIN(`pxb_nce_user_quest`.`catalog_id`) AS `cid`, `pxb_nce_user_quest`.`user_id` AS `uid` FROM `pxb_nce_user_quest` WHERE `pxb_nce_user_quest`.`user_id` = 335 GROUP BY `pxb_nce_user_quest`.`user_id` ORDER BY NULL

Example:

SomeModel.objects.annotate(Count('somecol'))

GROUP BY: all fields

SomeModel.objects.values ('name').annotate(Count('somecol'))

GROUP BY: name field, aggregate somecol

SomeModel.objects.annotate(Count('somecol')).values('name')

GROUP BY: all fields, query name

SomeModel.objects.values('name', 'pk').annotate(Count('somecol')).values('pk')

GROUP BY: name, pk field, Query the pk field

SomeModel.objects.values('name').annotate(Count('somecol')).values('pk')

GROUP BY: name, pk field, query the pk field

Associated knowledge:

The above query method may be difficult to understand at first, but if you compare the group by method of the original sql statement, you will find a similar principle

In the old version of mysql,

select a, b from t group by a will work normally, and the b field The first item will be automatically taken, which is equivalent to implicit aggregation. The above statement in the new version of mysql cannot work because the strict mode sql_mode=ONLY_FULL_GROUP_BY is enabled by default. The correct method is:

select a,max(b) as b from t group by, that is, the fields that need to be displayed to aggregate all queries

Comparing the new version of mysql syntax, you will find that it is very similar to the query method in ORM