Django: how to filter() after distinct()

Question

If we chain a call to filter() after a call to distinct(), the filter is applied to the query before the distinct. How do I filter the results of a query after applying distinct?

Example.objects.order_by('a','foreignkey__b').distinct('a').filter(foreignkey__b='something')

The where clause in the SQL resulting from filter() means the filter is applied to the query before the distinct. I want to filter the queryset resulting from the distinct.

This is probably pretty easy, but I just can't quite figure it out and I can't find anything on it.

Edit 1:

I need to do this in the ORM...

SELECT z.column1, z.column2, z.column3 FROM ( SELECT DISTINCT ON (b.column1, b.column2) b.column1, b.column2, c.column3 FROM table1 a INNER JOIN table2 b ON ( a.id = b.id ) INNER JOIN table3 c ON ( b.id = c.id) ORDER BY b.column1 ASC, b.column2 ASC, c.column4 DESC ) z WHERE z.column3 = 'Something';

(I am using Postgres by the way.) So I guess what I am asking is "How do you nest subqueries in the ORM? Is it possible?" I will check the documentation.

Sorry if I was not specific earlier. It wasn't clear in my head.

Answer 1

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.

Answer 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.