[Django]-Django ORM – Grouped aggregates with different select clauses

by

1👍

✅

This is the kind of queries that are easy to explain but hard to write. If this be SQL I will suggest to you a CTE filtered query with row rank over partition by language ordered by date ( desc )

But this is not SQL, this is django query api. Easy way is to do a query for each language:

languages = Meetup.objects.values("language", flat = True).distinct.order_by()
last_by_language = [  Meetup
                     .objects
                     .filter( language = l )
                     .latest( 'date' )
                     for l in languages
                    ]

This crash if some language don’t has meetings.
The other approach is to get all max data for each language:

last_dates = ( Meetup
             .objects
             .values("language")
             .annotate(ldate=models.Max("date"))
             .order_by() )

q= reduce(lambda q,meetup: 
     q | ( Q( language = meetup["language"] ) & Q( date = meetup["ldate"] ) ), 
     last_dates, Q())  

your_query = Meetup.objects.filter(q)

Perhaps someone can explain how to do it in a single query without raw sql.

Edited due OP comment

You are looking for:

"SELECT language, speaker, MAX(date) FROM app_meetup GROUP BY language"

Not all rdbms supports this expression, because all fields that are not enclosed into aggregated functions on select clause should appear on group by clause. In your case, speaker is on select clause (without aggregated function) but not appear in group by.

In mysql they are not guaranties than showed result speaker was that match with max date. Because this, we are not facing a easy query.

Quoting MySQL docs:

In standard SQL, a query that includes a GROUP BY clause cannot refer
to nonaggregated columns in the select list that are not named in the
GROUP BY clause…However, this is useful primarily when all values
in each nonaggregated column not named in the GROUP BY are the same
for each group.

The most close query to match your requirements is:

Reults = (   Meetup
             .objects
             .values("language","speaker")
             .annotate(ldate=models.Max("date"))
             .order_by() )
👤dani herrera

Leave a comment