Django ORM GROUP BY

Question

This PostgreSQL query solves my problem, but I'm not sure how to put it in Django ORM.

SELECT DISTINCT ON (to_email) * FROM (SELECT * FROM invitation_invitation ORDER BY date_invited DESC) AS sub

From a list of items, I want all distinct rows for "to_email" column, and where to_email is duplicate, it should select the latest ( date_invited )

Answer 1

Here's the overview on doing aggregation in Django ORM, and specifically the values functionality.

UPDATE: To put it in action, something similar to

Invitation.objects.values('to_email').annotate(date_invited=Max('date_invited'))

should work for your example.

UPDATE 2: Ferran is right that this will only retrieve the to_email field and date_invited annotation. If the other info is necessary right away, then it's probably easiest to do the 'only-the-latest' filtering in Python. The proposed solution is more suited to a situation where you initially display a list with only summary data and provide more details on request (assuming the summary data is enough to uniquely identify a record).

Answer 2

I think you cannot do this with django ORM with only one query. The ORM does'nt make this kind of subqueries. You can use the connection object and write the SQL yourself or make the subquery and get the DISTINCT step with python code.

Edit

The solution proposed by Hank Gay is ok but only gets the *date_invited* and *to_email* fields and the asked question is to get all the fields of the latest record.