Django ORM LEFT JOIN on fields with same values

Question

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

etc.

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

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;

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

Can you expand on the difference between `Observation` and `Measurement` and the relationship between the two? — Endre Both, Mar 13 '19 at 10:27
Once your data structure is clear, it should not be difficult to find a representation in Django that performs well. — Endre Both, Mar 13 '19 at 20:21
`Observation` contains information about hydropost and observer. Measurement is level, water temperature and etc. I will change my models, combine all measurements in `Measurement ` model — Yes Dslv, Mar 14 '19 at 03:58

Endre Both · Answer 1 · 2019-03-13T11:48:59.947

0

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.

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.

edited Mar 13 '19 at 11:48

answered Mar 13 '19 at 08:01

Endre Both

5,540
1
26
31

I tried your code, but result is different from SQL. The structure of database I saw in another database in my job, so i created this kind of model. You said, that for large datasets it is not suitable use tables with few fields. Do you have any articles or best practices related to database structure – Yes Dslv Mar 13 '19 at 10:23
What's the difference? – Endre Both Mar 13 '19 at 11:04
You are right, the join criteria were wrong. I changed `id` to `observation_id`. This is the kind of error you don't have to watch out for when you have your relationships well-defined in your Django models :). – Endre Both Mar 13 '19 at 11:45
Columns values precipitation, precipitation_type, water_temperature generated by django query and sql are different.in Outref, I tried put both 'id' and 'observation_id' – Yes Dslv Mar 13 '19 at 11:46
`observation_id=OuterRef('observation_id')` should work. – Endre Both Mar 13 '19 at 11:50

Django ORM LEFT JOIN on fields with same values

1 Answers1

Linked