具有相同值的字段上的Django ORM LEFT JOIN [英] Django ORM LEFT JOIN on fields with same values

问题描述

我正在为水文学家编写Web界面。水文学家应该看到带有不同水文测量的表格。

I am writing web-interface for hydrologists. Hydrologist should see table with different hydrological measurements like this.

+----------------+----------------------+-------+--------------------+-------------+------------------+
| observation_id | observation_datetime | level | water_temperature  |precipitation|precipitation_type|
+----------------+----------------------+-------+--------------------+-------------+------------------+

| 1 | 2019-03-11 11:00:00 | 11 | 21 | 31 |
| 2 | 2019-03-12 12:00:00 | 12 | 22 | 32 |
| 3 | 2019-03-13 13:00:00 | 13 | 23 | 33 |
| 4 | 2019-03-14 14:00:00 | 14 | 24 | 34 |

我有这些模型来描述测量

I have these models for describing measurements

class AbstractMeasurement(model.Model):
    observation_datetime = models.DateTimeField()
    observation = models.ForeignKey(Observation, on_delete = models.DO_NOTHING)

class Meta:
    abstract = True

class Level(AbstractMeasurement):
    level = models.DecimalField()

class WaterTemperature(AbstractMeasurement):
    air_temperature = models.DecimalField()

class Precipitation(AbstractMeasurement):
    precipitation = models.DecimalField()
    precipitation_type = models.CharField()

等。

水平测量和测量不能没有水平。级别是基本模型。

Level the main measurement and measurement cannot be done without level. Level is the basic model.

在mysql中，我可以通过此查询来完成

In mysql I can do it by this query

    SELECT level.observation_id, 
            level.observation_datetime, 
            level.level, 
            water_temperature.water_temperature, 
            precipitation.precipitation, 
            precipitation.precipitation_type 
    FROM level 
    LEFT JOIN precipitation ON 
            level.observation_datetime = precipitation.observation_datetime 
            AND 
            level.observation_id = precipitation.observation_id 
    LEFT JOIN water_temperature ON 
            level.observation_datetime = water_temperature.observation_datetime 
            AND 
            level.observation_id = water_temperature.observation_id;

如何使用没有外键关系的模型在django中左联接？

How I can LEFT JOIN in django with models without foreign key relationship?

推荐答案

您可以实现所需的功能，但是它的效率不必要地低（甚至比您发布的SQL查询还要低）。由于无论如何您当前的模型结构都是扭曲的，因此，如果您可以更改模型，则应该这样做。

You can achieve what you want, but it would be unnecessarily inefficient (even more so than the SQL query you posted). Since your current model structure is rather contorted anyway, if you can change the models, you should.

也就是说，这里介绍的方法是将降水数据添加到您的 Level 查询中。每个字段和行都需要一个子查询：

That said, here's how to bring e.g. Precipitation data into your Level query. You need one subquery per field and row:

from django.db.models import Q, OuterRef, Subquery

join_criteria = Q(
    observation_id=OuterRef('observation_id'), 
    observation_datetime = OuterRef('observation_datetime')
)

subquery_precipitation = Subquery(Precipitation.objects
    .filter(join_criteria)
    .values('precipitation')[:1])

subquery_precipitation_type = Subquery(Precipitation.objects
    .filter(join_criteria)
    .values('precipitation_type')[:1])

levels = (Level.objects
        .annotate(precipitation=subquery_precipitation)
        .annotate(precipitation_type=subquery_precipitation_type))

现在尝试将字段数与查询中的预期行数相乘–那就是需要执行的子查询的数量。

Now try multiplying the number of fields with the expected number of rows in your query – that's the number of subqueries that would need to be executed.

因此，这是一个概念证明，可用于少量小桌子和一些字段。它不适用于大型数据集和许多字段。您应该为此真正地重新考虑模型。

So this is a proof of concept you can use in a pinch for small tables and a few fields. It's not suitable for large data sets and many fields. You should really rethink your models for that.

使用适当的模型，应该很容易达到您的需求。

With proper models, it should be really easy to achieve what you need.

这篇关于具有相同值的字段上的Django ORM LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！