Django prefetch_related - 使用来自不同表的 or 子句过滤

Question

我有一个关系简单的模型

class Tasks(models.Model):
   initiator = models.ForeignKey(User, on_delete = models.CASCADE)

class TaskResponsiblePeople(models.Model):
    task = models.ForeignKey('Tasks')
    auth_user = models.ForeignKey(User)

我需要编写一个 SQL 查询的模拟，如下所示:

select a.initiator, b.auth_user 
from Tasks a
inner join TaskResponsiblePeople b
on TaskResponsiblePeople.task_id = task.id 
where Tasks.initiator = 'value A' OR TaskResponsiblePeople.auth_user = 'value B'

问题是 OR 语句处理两个不同的表，我不知道正确的 Django 语法来模拟上述原始 SQL 查询。请帮帮我!

更新 1

根据以下回答，我使用以下代码:

people = TaskResponsiblePeople.objects.filter(Q(task__initiator = request.user.id)|Q(auth_user = request.user.id)).select_related('auth_user')
print people.query
# The result of the print copy-pasted from console
# SELECT * FROM `task_responsible_people` 
# LEFT OUTER JOIN `tasks` ON (`task_responsible_people`.`task_id` = `tasks`.`id`)
# LEFT OUTER JOIN `auth_user` T4 
# ON (`task_responsible_people`.`auth_user_id` = T4.`id`) 
# WHERE (`tasks`.`initiator_id` = 7 OR    
# 'task_responsible_people`.`auth_user_id` = 7)

tasks = Tasks.objects.prefetch_related(
              Prefetch('task_responsible_people', queryset=people, to_attr='people'))

但是，在最终结果集中，我仍然可以看到启动器和 auth_user 都不等于 request.user 的记录(在这种情况下等于 7)
我避免使用“.values”，因为可能需要将查询集序列化并将其转换为 json。

Answer 1

如果您只想要这些特定列，我认为您可以这样做:

from django.db.models import Q

qs = Tasks.objects.filter(Q(initiator=userA) | Q(taskresponsiblepeople__auth_user=userB))\
          .values('initiator', 'taskresponsiblepeople__auth_user')

要检查生成的查询，您可以查看:

print(qs.query)

我的数据库中没有模型，但它应该生成类似于以下内容的查询:

SELECT "tasks"."initiator_id", "taskresponsiblepeople"."auth_user_id" 
    FROM "tasks" LEFT OUTER JOIN "taskresponsiblepeople" 
    ON ( "tasks"."id" = "taskresponsiblepeople"."tasks_id" ) 
    WHERE  ("tasks"."initiator_id" = userA_id 
            OR "taskresponsiblepeople"."auth_user_id" = userB_id))