时间戳

首页 > 解决方案 > 如何在 Django ORM 中表示复杂的子查询和计算表？

django - 如何在 Django ORM 中表示复杂的子查询和计算表？

问题描述

我有以下基本上可以工作的 SQL。我将如何在 Django ORM 中表示这一点？我想避免运行完整的原始查询

我不确定如何处理 Django ORM 中的子查询以及如何正确执行笛卡尔积（通过 CROSS JOIN 实现）

SELECT datum,
       alldata.worker_id,
       reporting_plan.project_id,
       SUM(effort::float)/60/60
FROM
  (SELECT DISTINCT datum,
                   reporting_plan.worker_id AS worker_id
   FROM
     (SELECT datum::date
      FROM generate_series('2019-05-01', '2019-12-31', '1 day'::interval) datum) AS dates
   CROSS JOIN reporting_plan
   ORDER BY datum,
            worker_id) AS alldata
LEFT OUTER JOIN reporting_plan ON alldata.worker_id = reporting_plan.worker_id
AND datum <= reporting_plan.end
AND datum >= reporting_plan.start
GROUP BY datum,
         alldata.worker_id,
         reporting_plan.worker_id,
         reporting_plan.project_id
ORDER BY datum,
         alldata.worker_id,
         reporting_plan.worker_id,
         reporting_plan.project_id

预期结果是一个列表，其中包含时间范围内的所有日期以及所有工人和匹配的计划信息（项目和工作量）。

谢谢！

编辑：

根据@jimjimjim 的反馈，我设法在获得相同结果的同时删除了 CROSS JOIN：

SELECT datum::date,
       alldata.worker_id,
       reporting_plan.project_id,
       SUM(effort::float)/60/60
FROM
  (SELECT DISTINCT generate_series ('2019-05-01', '2019-12-31', '1 day'::interval) AS datum,
                   worker_id
   FROM reporting_plan
   ORDER BY datum,
            worker_id) AS alldata
LEFT OUTER JOIN reporting_plan ON alldata.worker_id = reporting_plan.worker_id
AND datum <= reporting_plan.end
AND datum >= reporting_plan.start
GROUP BY datum,
         alldata.worker_id,
         reporting_plan.worker_id,
         reporting_plan.project_id
ORDER BY datum,
         alldata.worker_id,
         reporting_plan.project_id

标签： djangodjango-orm

解决方案

您的问题与 Reddit 的问题有点不同，通过这种方法，您将进行原始查询但返回 ORM 对象。我将执行以下操作：

  1. 为查询创建模型：
    class WorkerEffort(models.Model):
        date = models.DateField(...)
        worker = models.ForeignKey(Worker, db_column="worker_id")
        project = models.ForeignKey(Project, db_column="project_id")
        effort = models.DecimalField()

        class Meta:
            managed = False

此处管理信息：https ://docs.djangoproject.com/en/2.2/ref/models/options/#managed

  1. 对该模型执行原始查询：
WorkerEffort.objects.raw('''
SELECT datum,
       alldata.worker_id,
       reporting_plan.project_id,
       SUM(effort::float)/60/60
FROM
  (SELECT DISTINCT datum,
                   worker_id
   FROM
     (select *, generate_series(start, end, '1 day'::interval) as datum from reporting_plan) 
     ) AS alldata
LEFT OUTER JOIN reporting_plan ON alldata.worker_id = reporting_plan.worker_id
GROUP BY datum,
         alldata.worker_id,
         reporting_plan.worker_id,
         reporting_plan.project_id
ORDER BY datum,
         alldata.worker_id,
         reporting_plan.worker_id,
         reporting_plan.project_id
''')

使用原始查询，您将能够通过 WorkerEffort（查询模型）将 ID 与 ORM 对象匹配。

推荐阅读