Django查询与distinct和order_by [英] Django query with distinct and order_by

问题描述

我有两个模型

class Employer(models.Model):
     name = models.CharField(max_length=300, blank=False)
     id = models.IntegerField()
     status = models.IntegerField()
     eminence = models.IntegerField(null=False,default=4)

class JobTitle(models.Model)
     name = models.CharField(max_length=300, blank=False)
     employer = models.ForeignKey(Employer,unique=False,null=True)
     activatedate = models.DateTimeField(default=datetime.datetime.now)

我需要所有雇主的职位名称最后激活。

I need all employers in the order of whose jobtitle activated last.

Employer.objects.filter(status=1).order_by('eminence','-jobtitle__activatedate')

这个查询给了我所需要的，但如果雇主有多个职位。

This query gives me what I want but it returns repeated employers if employer has more than one jobtitle.

我将使用 distinct（），但在Django文档中，我发现

I would use distinct() but in Django documents I found that

*在order_by（）调用中使用的任何字段都包含在SQL SELECT列中。当与distinct（）结合使用时，有时会导致意想不到的结果。如果您通过相关模型的字段进行排序，那么这些字段将被添加到所选列，并且它们可能会使得重复行显示为不同。由于额外的列不会显示在返回的结果中（它们只是在那里支持排序），所以有时候看起来就像返回非不同的结果。*

*Any fields used in an order_by() call are included in the SQL SELECT columns. This can sometimes lead to unexpected results when used in conjunction with distinct(). If you order by fields from a related model, those fields will be added to the selected columns and they may make otherwise duplicate rows appear to be distinct. Since the extra columns don't appear in the returned results (they are only there to support ordering), it sometimes looks like non-distinct results are being returned.*

虽然他们解释了我的问题，但没有指定任何解决方案。

Although they explained my problem no solution is specified.

可以给我一个建议，如何分组我的雇主列表，而不会破坏API稳定性？

Could give me a suggestion how can I group by my employer list without corrupting the API stability?

推荐答案

你可以放在雇主类上，最新的 JobTitle 激活日期，然后按此字段排序而不使用关系。[1]这里的权衡是一些数据重复，当对应的 JobTitle 实例更改时，手动更新雇主的最新职位激活日期的必要性。

You can place on the Employer class its latest JobTitle activation date, and then order by this field without using relations.[1] The tradeoff here is a bit of data duplication, and the necessity to update employer's latest job title activation date manually when corresponding JobTitle instance changes.

另外，请检查这篇文章，使用 annotate（）的另一个解决方案。

Also, check this post for another solution which uses annotate().

相关问题：

[1] Queryset API distinct（）不起作用？

这篇关于Django查询与distinct和order_by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！