Django-ORM：外键属性元组的过滤器

Question

我正在写一个匹配的服务，它将根据重叠的专业知识为您匹配配置文件。E、 g.如果您需要python专业知识，它将与提供python专业知识的人匹配。在

我很难确定如何用Django ORM构造查询。在

设置

我有模型：

class Profile(models.Model):
    pass

class Expertise(models.Model):
    profile = db.ForeignKey('profile', on_delete=models.CASCADE)
    name = db.CharField(choices=[(1, 'python'), (2, 'javascript'), (3, 'golang')], max_length =255)
    direction = db.CharField(choices=[('offered', 'offered'), ('requested', 'requested')], max_length = 255)

数据

我得到的数据基本上如下所示：

^{pr2}$

显然是错误的尝试

Profile.objects.filter(
   expertise__name__in = [e.name for e in profile.expertise_set()]
   expertise__direction__in = [e.direction for e in profile.expertise_set()]
)

实际上，我在寻找在查询中组合boolean和AND boolean或的能力。在

在另一种技术中，我会

在SQL中，我会按照以下思路做一些事情：

SELECT * FROM app_profiles JOIN app_expertise on app_profiles.id = app_expertise.app_profiles_id 
WHERE 
   (app_expertise.direction = 'offered' AND app_expertise.name = 1) OR
   (app_expertise.direction = 'requested' AND app_expertise.name = 2)

预期行为

>>> maus = Profile.objects.get_or_create(name='Maus')[0]
>>> kungphu = Profile.objects.get_or_create(name='kungphu')[0]
>>> strange = Profile.objects.get_or_create(name='Dr. Strange')[0]
>>> thanos = Profile.objects.get_or_create(name='thanos')[0]
>>> 
>>> # maus offers golang and requests python
>>> Expertise.objects.get_or_create(
...     profile=maus, 
...     name=Expertise.NAME_PYTHON, 
...     direction=Expertise.DIRECTION_REQUESTED,
... )[0]
<Expertise: Python (Requested by Maus)>
>>> Expertise.objects.get_or_create(
...     profile=maus, 
...     name=Expertise.NAME_GOLANG, 
...     direction=Expertise.DIRECTION_OFFERED,
... )[0]
<Expertise: Golang (Offered by Maus)>
>>> # kungphu offers python and requests golang, they will match 
>>> # with mous both because of golang and python compatibility
>>> Expertise.objects.get_or_create(
...     profile=kungphu, 
...     name=Expertise.NAME_PYTHON, 
...     direction=Expertise.DIRECTION_OFFERED,
... )[0]
<Expertise: Python (Requested by kungphu)>
>>> Expertise.objects.get_or_create(
...     profile=kungphu, 
...     name=Expertise.NAME_GOLANG, 
...     direction=Expertise.DIRECTION_REQUESTED,
... )[0]
<Expertise:Golang (Requested by kungphu)>
>>> # Doctor Strange is trying to learn golang, he will match with maus as 
>>> # a result.
>>> Expertise.objects.get_or_create(
...     profile=kungphu, 
...     name=Expertise.NAME_GOLANG, 
...     direction=Expertise.DIRECTION_REQUESTED,
... )[0]
<Expertise:Golang (Requested by strange)>
>>> 
>>> # Thanos both offers and requests Python, because balance, I guess.
>>> Expertise.objects.get_or_create(
...     profile=thanos, 
...     name=Expertise.NAME_PYTHON, 
...     direction=Expertise.DIRECTION_REQUESTED,
... )[0]
<Expertise: Python (Requested by thanos)>
>>> Expertise.objects.get_or_create(
...     profile=thanos, 
...     name=Expertise.NAME_PYTHON, 
...     direction=Expertise.DIRECTION_OFFERED,
... )[0]
<Expertise: Python (Offered by thanos)>
>>> 
>>> # maus has requested Python, thanos and kungphu have offered it
>>> # maus has offered golang, dr. strange and kungphu have requested it.
>>> maus.find_matches()
[<Profile: kungphu>, <Profile: thanos>, <Profile: strange>]

Answer 1

对于复杂的查询，Django提供了^{} objects

因此，您可以使用Q对象查询该表，例如

Expertise.objects.select_related('profile').filter(
    (Q(direction='offered') & Q(name=1)) | (Q(direction='requested') & Q(name=2))
)

|是OR，而{}是不言而喻的。您还可以对NOT样式的查询执行~Q。在

select_related，如果您没有遇到它，则可以对相关对象进行更有效的查询。你可以在这里阅读更多信息；https://medium.com/@lucasmagnum/djangotip-select-prefetch-related-e76b683aa457