在django中组合order_by和distinct

时间:2021-07-27 22:48:55

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.

我读了很多关于它的内容,我可以理解这样的实现缺失了,但我无法理解为什么我不能用Django来模拟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.

我知道PostgreSQL不支持DISTINCT ON而没有ORDER BY子句中的列。但是我可以通过在DISTINCT的子选择上执行ORDER BY来达到我想要的结果。我想在Django中做同样的事情,因为在这种情况下的性能没有问题。但Django QuerySets(太)懒惰。

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.

但遗憾的是,即使尝试通过count()调用强制执行第一个查询,它也会尝试在同一查询中执行distinct和order_by,这是失败的。

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

因此,基于此,我必须回到python排序来做同样的事情:

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 !

如果有人有一个更优雅的解决方案,一种强制Django提交第一个SELECT DISTINCT的方法,我会很乐意拥有它。谢谢 !

1 个解决方案

#1


1  

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.

在order_by方法中,您可以尝试添加“md5_checksum”作为第一个参数。这可能有助于它正确排序,除非我误解了这个问题。

#1


1  

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.

在order_by方法中,您可以尝试添加“md5_checksum”作为第一个参数。这可能有助于它正确排序,除非我误解了这个问题。