[Fixed]-Django: how to filter() after distinct()

by

2👍

✅

Thanks a ton for the help guys. I tried both suggestions and could not bend either of those suggestions to work, but I think it started me in the right direction.

I ended up using 

from django.db.models import Max, F

Example.objects.annotate(latest=Max('foreignkey__timefield')).filter(foreignkey__timefield=F('latest'), foreign__a='Something')

This checks what the latest foreignkey__timefield is for each Example, and if it is the latest one and a=something then keep it. If it is not the latest or a!=something for each Example then it is filtered out.

This does not nest subqueries but it gives me the output I am looking for – and it is fairly simple. If there is simpler way I would really like to know.

👤colboynik

12👍

This is an old question, but when using Postgres you can do the following to force nested queries on your ‘Distinct’ rows:

foo = Example.objects.order_by('a','foreign_key__timefield').distinct('a')
bar = Example.objects.filter(pk__in=foo).filter(some_field=condition)

bar is the nested query as requested in OP without resorting to raw/extra etc. Tested working in 1.10 but docs suggest it should work back to at least 1.7.

My use case was to filter up a reverse relationship. If Example has some ForeignKey to model Toast then you can do:

Toast.objects.filter(pk__in=bar.values_list('foreign_key',flat=true))

This gives you all instances of Toast where the most recent associated example meets your filter criteria.

Big health warning about performance though, using this if bar is likely to be a huge queryset you’re probably going to have a bad time.

👤Keith Bailey

1👍

No you can’t do this in one simple SELECT.
As you said in comments, in Django ORM filter is mapped to SQL clause WHERE, and distinct mapped to DISTINCT. And in a SQL, DISTINCT always happens after WHERE by operating on the result set, see SQLite doc for example.

But you could write sub-query to nest SELECTs, this depends on the actual target (I don’t know exactly what’s yours now..could you elaborate it more?)

Also, for your query, distinct('a') only keeps the first occurrence of Example having the same a, is that what you want?

👤okm

Leave a comment