Django prefetch_related - filter with or-sentence from different tables

Question

I have a model with a simple ratio

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 the 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 don't know how to use the Django syntax correctly to reproduce the above raw-SQL query. Help me please!

UPDATE 1

According to the answer below, I am using 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 result set, I still see entries where neither the initiator nor auth_user are equal to request.user (equal to 7 in this case) I avoid using ".values" due to the potential need to serialize and convert the request to json.

Answer 1

I think you can do it this way if you just need 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 view the generated request, you can see:

 print(qs.query) 

I have no models in my database, but it should generate a query similar to the 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))