How to do nested Group By with Annotation in django orm?-CodePudding

I have the following data:

publisher                    title
--------------------------  -----------------------------------
New Age Books                Life Without Fear
New Age Books                Life Without Fear
New Age Books                Sushi, Anyone?
Binnet & Hardley             Life Without Fear
Binnet & Hardley             The Gourmet Microwave
Binnet & Hardley             Silicon Valley
Algodata Infosystems         But Is It User Friendly?
Algodata Infosystems         But Is It User Friendly?
Algodata Infosystems         But Is It User Friendly?

Here is what I want to do: I want to count how many books of the same titles are published by each author. I want to get the following result:

{publisher: New Age Books, title: Life Without Fear, count: 2},
{publisher: New Age Books, title: Sushi Anyone?, count: 1},
{publisher: Binnet & Hardley, title: The Gourmet Microwave, count: 1},
{publisher: Binnet & Hardley, title: Silicon Valley, count: 1},
{publisher: Binnet & Hardley, title: Life Without Fear, count: 1},
{publisher: Algodata Infosystems, title: But Is It User Friendly?, count: 3}

My solution goes something along the lines of:

query_set.values('publisher', 'title').annotate(count=Count('title'))

But it is not producing the desired result.

CodePudding user response：

There is a peculiarity in Django that will not perform a GROUP BY on the values without an .order_by() clause. You can thus add an .order_by() clause and process this with:

query_set.values('publisher', 'title').annotate(
    count=Count('pk')
).order_by('publisher', 'title')

By ordering the items "fold" into a group and we thus count the number of primary keys for each group.