如何在Django查询集中过滤ManyToMany字段？

Question

我有模型：

class VideoParameter(models.Model):
    member = models.ForeignKey(Tag, on_delete=models.DO_NOTHING, related_name="parameter_tag")
    value = models.PositiveIntegerField()


class Video(TimeStampedModel):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    parameters = models.ManyToManyField(VideoParameter, related_name="video_parameter")
    url = models.URLField(verbose_name=_('Url'))

（我的意思是一个视频有一些参数，比如键值对（例如{"rabbit": 1, "panda": 1, "lion": 0}））

然后我试图通过查询参数获取视频。

（像这样/video/?rabbit=1&panda=1&lion=0）

我想要执行的SQL代码如下：

SELECT app_video.id, count(*) AS match_count FROM app_video 
INNER JOIN app_video_parameters on app_video.id = app_video_parameters.video_id
INNER JOIN app_videoparameter ON app_video_parameters.videoparameter_id = app_videoparameter.id 
WHERE (app_videoparameter.member_id = 1 AND app_videoparameter.value = 0) 
OR (app_videoparameter.member_id = 9 AND app_videoparameter.value = 1)
OR (app_videoparameter.member_id = 11 AND app_videoparameter.value = 1)
...
GROUP BY (app_video.id) 
ORDER BY match_count DESC;

如何在Django的查询集中实现这个过程？

- swdrsk

---

那么，任何一件物品都足够了吗？ - undefined

Answer 1

你可以首先使用以下代码构建一个Q对象：

from django.db.models import Count, Q

data = {'rabbit': 1, 'panda': 1, 'lion': 0}
q = Q(
    *[
        <b>Q(parameters__member__name=k, parameters__value=v)</b>
        for k, v in data.items()
    ],
    _connector=Q.OR
)
Video.objects.filter(q).annotate(
    match_count=Count('parameters')
).order_by('-match_count')

- willeM_ Van Onsem