使用 Django ORM 查询这些数据的最佳方式是什么？

Question

我有一个类似的模型：

class Task(models.Model):
    created_by = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='tasks_by_me')
    assignees = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name='tasks_assigned_to_me')

我想获取所有任务(无重复(是由Theuser创建或分配给他的>"。

这是我可以在SQL中进行编码的方法：

SELECT *
FROM
  (
    SELECT 1 AS id, 1 as created_by_id UNION ALL
    SELECT 2 AS id, 2 as created_by_id UNION ALL
    SELECT 3 AS id, 3 as created_by_id UNION ALL
    SELECT 4 AS id, 2 as created_by_id
  ) task
LEFT JOIN
  (
    SELECT 1 AS task_id, 1 AS user_id UNION ALL
    SELECT 1 AS task_id, 2 AS user_id UNION ALL
    SELECT 2 AS task_id, 1 AS user_id UNION ALL
    SELECT 2 AS task_id, 2 AS user_id UNION ALL
    SELECT 2 AS task_id, 3 AS user_id UNION ALL
    SELECT 3 AS task_id, 3 AS user_id UNION ALL
    SELECT 4 AS task_id, 2 AS user_id
  ) task_assignees ON task_assignees.task_id = task.id AND COALESCE(task_assignees.user_id, 2) = 2
WHERE task.created_by_id = 2
OR    task_assignees.user_id = 2;

输出：

id  created_by_id   task_id     user_id
1   1               1           2
2   2               2           2
4   2               4           2

NOTE ：task_id=2只有1行，尽管它有3个受让人，并且该任务是由TheUser(id=2)

创建的

我对处理这些方案的最佳实践感兴趣？

Answer 1

您可以使用QuerySet API来使用Django Orm模型，而不是编写RAW SQL查询。下面的示例将为您提供给您分配给您的任务或由您创建的任务的QuerySet。Q用于在Django Orm中执行复杂查询

from django.db.models import Q
Task.objects.filter(Q(assignees__username='soemeusername') | Q(created_by__username='someusername')).distinct()