CODEWITHSUNDEEP
djangodjango-modelsdjango-aggregationdjango-filters
pkdkk
pkdkk

Reputation: 3961

Django order_by() filter with distinct()

How can I make an order_by like this ....

p = Product.objects.filter(vendornumber='403516006')\
                   .order_by('-created').distinct('vendor__name')

The problem is that I have multiple vendors with the same name, and I only want the latest product by the vendor ..

Hope it makes sense?

I got this DB error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT DISTINCT ON ("search_vendor"."name") "search_product"...

Upvotes: 78

Views: 60497

Answers (4)

Dmitriy Sintsov
Dmitriy Sintsov

Reputation: 4159

Just matching leftmost order_by() arg and distinct() did not work for me, producing the same error (Django 1.8.7 bug or a feature)?

qs.order_by('project').distinct('project')

however it worked when I changed to:

qs.order_by('project__id').distinct('project')

and I do not even have multiple order_by args.

Upvotes: 34

kophygiddie
kophygiddie

Reputation: 1312

In case you are hoping to use a separate field for distinct and order by another field you can use the below code

from django.db.models import Subquery

Model.objects.filter(
    pk__in=Subquery(
       Model.objects.all().distinct('foo').values('pk')
    )
).order_by('bar')

Upvotes: 29

SaeX
SaeX

Reputation: 18691

I had a similar issue but then with related fields. With just adding the related field in distinct(), I didn't get the right results.

I wanted to sort by room__name keeping the person (linked to residency ) unique. Repeating the related field as per the below fixed my issue:

.order_by('room__name', 'residency__person', ).distinct('room__name', 'residency__person')

See also these related posts:

Upvotes: 4

janos
janos

Reputation: 124646

Based on your error message and this other question, it seems to me this would fix it:

p = Product.objects.filter(vendornumber='403516006')\
               .order_by('vendor__name', '-created').distinct('vendor__name')

That is, it seems that the DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). So by making the column you use in distinct as the first column in the order_by, I think it should work.

Upvotes: 98

Related Questions