CODEWITHSUNDEEP
djangopostgresqlsql-order-bydistinct
Rmatt
Rmatt

Reputation: 1347

Combining order_by and distinct in django

Hi all,

I read quite a bit about it, I can understand that such an implementation is missing, but I can't get why I can't, with Django, emulate the behaviour ob a SELECT (SELECT DISTINCT[...]) ORDER BY.

I know that PostgreSQL does not support a DISTINCT ON without having the column in the ORDER BY clause. But I can reach the result I want by doing the ORDER BY on the subselect of the DISTINCT. And I would like to do same in Django, as performance in this case no issue is. But Django QuerySets are (too) lazy.

I would have loved to be able to run this :

b = Backup.objects.filter(state__iexact='up').distinct('md5_checksum').order_by('-create_date', '-title')

Or at least this:

b = Backup.objects.filter(state__iexact='up').distinct('md5_checksum')
if b.count() > 6:
    b = b.order_by('-create_date', '-title')

But unfortunately, even with the try to force the execution of the first query through the count() call, it tries to do the distinct and the order_by in the same query, which is failing.

Therefore, based on this, I have to go back to python sorting to do the same:

b = sorted(b, key=lambda x: (x.create_date, x.title), reverse=True)

If anybody had a more elegant solution, a way to force Django to commit the first SELECT DISTINCT, I would be please to have it. Thanks !

Upvotes: 1

Views: 1427

Answers (1)

Dan Breen
Dan Breen

Reputation: 12924

In the order_by method, you might try adding 'md5_checksum' as the first parameter. That might help it order correctly, unless I'm misunderstanding the question.

Upvotes: 1

Related Questions