Django Query, distinct on foreign key

Question

Given these models

class User(Model):
  pass

class Post(Model):
  by = ForeignKey(User) 
  posted_on = models.DateTimeField(auto_now=True)

I want to get the latest Posts, but not all from the same User, I have something like this:

posts = Post.objects.filter(public=True) \
        .order_by('posted_on') \
        .distinct("by")

But distinct doesn't work on mysql, I'm wondering if there is another way to do it? I have seen some using values(), but values doesn't work for me because I need to do more things with the objects themselves

Mirza715 · Answer 1 · 2020-01-29T11:16:04.943

0

order_by should match the distinct(). In you case, you should be doing this:

posts = Post.objects.filter(public=True) \
        .order_by('by') \
        .distinct('by')

.distinct([*fields]) only works in PostgresSQL.

For MySql Engine. This is MySQL documentation in Django:

Here's the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names.

For MySql workaround could be this:

from django.db.models import Subquery, OuterRef


user_post = Post.objects.filter(user_id=OuterRef('id')).order_by('posted_on')            
post_ids = User.objects.filter(related_posts__isnull=False).annotate(post=Subquery(user_post.values_list('id', flat=True)[:1]))).values_list('post', flat=True)
posts = Post.objects.filter(id__in=post_ids)

edited Jan 29 '20 at 11:16

answered Jan 29 '20 at 10:20

Mirza715

420
5
15

This will not work with MySQL DB engine - you'll get `NotSupportedError: DISTINCT ON fields is not supported by this database backend` – Charnel Jan 29 '20 at 10:27
MySql does not support distinct by "field name". You can only distinct but not by any particular field. – Mirza715 Jan 29 '20 at 10:33
in your mysql workaround, I can't see where/how are you making sure the posts have unique users? – Reed Jones Jan 30 '20 at 09:41

Charnel · Accepted Answer · 2020-01-29T10:48:10.853

0

Since distinct will not work with MySQL on other fields then model id, this is possible way-around with using Subquery:

from django.db.models import Subquery, OuterRef
...
sub_qs = Post.objects.filter(user_id=OuterRef('id')).order_by('posted_on')
# here you get users with annotated last post
qs = User.objects.annotate(last_post=Subquery(sub_qs[:1]))
# next you can limit the number of users

Also note that ordering on posted_on field depends on your model constraints - perhaps you'll need to change it to -posted_on to order from newest on top.

edited Jan 29 '20 at 10:48

answered Jan 29 '20 at 10:41

Charnel

4,222
2
16
28

I'm not sure what subquery is doing here I'll look into it though... so the result here is a bunch of users with last post which kind of answers the question, so I'll mark it answered. thanks – Reed Jones Jan 30 '20 at 09:44

Django Query, distinct on foreign key

2 Answers2