Django prefetch_related - filter with or-clause from different tables

Question

I have a model with simple relation

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)

And I need to write an analogue of an SQL query as follows:

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'

The problem is that the OR statement deals with two different tables and I've got no idea about the right Django syntax to mimique the above-stated raw-SQL query. Help me out please !

UPDATE 1

According to the below-stated answer, I use the following code:

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'))

However, in the final resultset I can still see records where neither initiator nor auth_user are equal to request.user (equal to 7 in this case) I avoid using ".values" because of the potential need to serialize and transform the queryset into json.

Answer 1

I think you can do it this way if you just want those specific columns:

from django.db.models import Q

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

To examine the generated query you can look at:

print(qs.query)

I don't have the models in my database but it should generate a query similar to following:

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))