Grouping aggregations in Django

Question

For reporting I want to group some counts together but still have access to the row data. I feel like I do this a lot but can't find any of the code today. This is in a B2B video delivery platform. A simplified version of my modelling:

Video(name)
Company(name)
View(video, company, when)

I want to show which companies viewed the most videos, but also show which Videos were the most popular.

+-----------+-----------+-------+
|  Company  |   Video   | Views |
+-----------+-----------+-------+
| Company E |  Video 1  |  1215 |
|           |  Video 2  |     5 |
|           |  Video 3  |     1 |
| Company B |  Video 2  |   203 |
|           |  Video 4  |     3 |
+-----------+-----------+-------+

I can do that by annotating views (to get the right to a company order, most views first) and then looping companies and performing a View query for each company, but how can I do this in in O(1-3) queries rather than O(n)?

@iklinac I did, very simplified. Video, Company and a log models View that ties the two together. — Oli, Jul 20 '20 at 12:31
I would suggest you to go with values, aggregate, and select_related of company. Didn't bother to write full solution as I asked for Models so that I don't need to rewrite them :) — iklinac, Jul 20 '20 at 12:38

score 0 · Answer 1 · answered Jul 20 '20 at 12:34

So I think I got there with .values(). I always seem to trip over this and it does mean I'll have to do a couple of additional lookups to pull out the video and company names... But 3 feels healthier than n queries.

results = (
    Video.objects
    .values('views__company_id', 'id')
    .annotate(
        view_count=Count('views', filter=q_last_month)  # filter count based on last month
    )
)

Grouping aggregations in Django

1 Answers1