Django query: get the last entries of all distinct values by list

Question

I am trying to make a Django query for getting a list of the last entries for each distinct values from a MySQL database. I will show an example below as this explanation can be very complicated. Getting the distinct values by themselves obviously in Django is no problem using .values(). I was thinking to create couple of Django queries but that looks to be cumbersome. Is there an easy way of doing this.

For the example below. Suppose I want the rows with distinct Names with their last entry(latest date).

Name            email                 date
_________________________________________________

Dane            [email protected]        2017-06-20
Kim             [email protected]         2017-06-10
Hong            [email protected]        2016-06-25
Dane            [email protected]        2017-06-04
Susan           [email protected]       2017-05-21
Dane            [email protected]        2017-02-01
Susan           [email protected]         2017-05-20

All the distinct values are Dane, kim, Hong, Susan. I also want the rows with the latest dates associated with these distinct name. The list with entries I would like is the rows below. Notice Names are all distinct, and they are associated with the latest date.

 Name            email                 date
_________________________________________________

Dane            [email protected]        2017-06-20
Kim             [email protected]         2017-06-10
Hong            [email protected]        2016-06-25
Susan           [email protected]       2017-05-21

Dimas Ari · Answer 1 · 2020-06-15T02:11:19.710

2

with postgresql you should able to do:

 EmailModel.objects.all().order_by('date').distinct('Name')

for more methods/functions like this, you can visit the docs here

edited Jun 15 '20 at 02:11

answered Jun 21 '17 at 01:57

Dimas Ari

148
1
8

1

This does not work, but I am following your input, as it looks like it is going in the right direction. Thanks. – Jam1 Jun 21 '17 at 02:18
yes its not exact code. it depends on your EmailModel looks like – Dimas Ari Jun 21 '17 at 02:22
1

It does not work. django.db.utils.NotSupportedError: DISTINCT ON fields is not supported by this database backend – sinkmanu Jun 14 '20 at 16:42
How to apply filtering over it? for ex- i don't want to include it in the result if the first email id is [email protected] for Dave and only include it if it is something different. – raj-kapil Nov 30 '22 at 05:05

pypypy · Answer 2 · 2017-06-21T01:33:26.180

1

This only applies to POSTGRES

You can use the ORDER_BY command to set your query set as ordered by date, then chain with the DISTINCT command to get distinct rows and specify which field. The DISTINCT command will take the first entry for each name. Refer The Docs For More

Edit

For MYSQL, you will have to use raw SQL queries, refer here

edited Jun 21 '17 at 01:33

answered Jun 21 '17 at 01:22

pypypy

1,075
8
18

Thank you sir, but I am using `MySQL`. I will keep playing around with it. Come to think of it I was using `order_by` and ran into some command line problem on the `Python` console – Jam1 Jun 21 '17 at 01:25

score 1 · Answer 3 · answered Jun 21 '17 at 01:47

1

Only Postgres supports providing field names in distinct. Also, any field provided in values and order_by is in distinct, thus providing ambiguous results sometimes.

However for MySQL:

Model.objects.values('names').distinct().latest('date')

answered Jun 21 '17 at 01:47

Raj Subit

1,487
2
12
23

Django query: get the last entries of all distinct values by list

3 Answers3

Linked