Django相交计数注释以进行排序 [英] Django intersect count annotate for sorting

问题描述

在此问题中，我们获得了对查询进行排序的解决方案基于两个多对多字段的交集。虽然这是一个很好的答案，但限制是您必须先过滤。

In this question, we were given a solution to sort a query based on the intersection of two many to many fields. While it's a great answer, the restriction is that you have to filter first.

说我有两个相同的模型。有没有一种方法可以通过相交计数来注释所有结果，以便我可以显示所有问题，而无论位置如何，但仍按位置排序？

Say I have the same two models. Is there a way to annotate all results by count of intersection, so that I can display all Questions regardless of location, but still sorted by location?

class Location(models.Model):
    name = models.CharField(max_length=100)


class Profile(models.Model):
    locations_of_interest = models.ManyToManyField(Location)


class Question(models.Model):
    locations = models.ManyToManyField(Location)

我希望我可以做这样的事情：

I wish I could do something like this:

from django.db.models import Count

matching_profiles = Profile.objects.all().annotate(
    locnom=Count('locations_of_interest__in=question.locations.all()')
)

有什么想法吗？我是否只需要进行两个查询并将它们合并？

Any ideas? Do I just have to make two queries and merge them?

推荐答案

我们可以在中移动过滤条件Count 函数：

Profile.objects.annotate(
    locnom=Count('id', filter=Q(locations_of_interest__in=question.locations.all()))
)

在 no 与<$ c相关的个人资料 s与位置或问题没有位置的情况下，仍将包括在内，在这种情况下，该个人资料中的.locnom 将为 0 。

The two are not equivalent in the sense that Profiles with no related Locations, or in case the question has no location, will still be included, in that case the .locnom of that Profile will be 0.

查询大致类似于：

SELECT profile.*,
       COUNT(CASE WHEN proloc.location_id IN (1, 4, 5) THEN profile.id ELSE NULL)
           AS locnom
FROM profile
LEFT OUTER JOIN profile_locations_of_interest AS proloc
             ON profile.id = proloc.profile_id
GROUP BY profile.id

其中 [1、4、5] 在这里示例 id id s相关的 Location s c $ c>问题。

Where [1, 4, 5] are here sample ids of the related Locations of the question.

这篇关于Django相交计数注释以进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！