Django: Single query with multiple joins on the same one-to-many relationship

Question

Using the Django QuerySet API, how can I perform multiple joins between the same two tables/models? See the following untested code for illustration purposes:

class DataPacket(models.Model):
    time = models.DateTimeField(auto_now_add=True)

class Field(models.Model):
    packet = models.ForeignKey(DataPacket, models.CASCADE)
    name = models.CharField(max_length=25)
    value = models.FloatField()

I want to grab a list of data packets with only specific named fields. I tried something like this:

pp = DataPacket.prefetch_related('field_set')
result = []
for p in pp:
    o = {
        f.name: f.value
        for f in p.field_set.all()
        if f.name in ('latitude', 'longitude')
    }
    o['time'] = p.time
    result.append(o)

But this has proven extremely inefficient because I'm working with hundreds to thousands of packets with a lot of other fields besides the latitude and longitude fields I want.

Is there a Django QuerySet call which translates into an efficient SQL query performing two inner joins from the datapacket table to the field table on different rows? I can do it with raw SQL, as follows (assuming the Django application is named myapp) (again, untested code for illustration purposes):

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute('''
        SELECT p.time AS time, f1.value AS lat, f2.value AS lon
        FROM myapp_datapacket AS p
        INNER JOIN myapp_field as f1 ON p.id = f1.packet_id
        INNER JOIN myapp_field as f2 ON p.id = f2.packet_id
        WHERE f1.name = 'latitude' AND f2.name = 'longitude'
    ''')
    result = list(cursor)

But instinct tells me not to use the low-level DB api if I don't have to do so. Possible reasons to back that up might be that my SQL code might not be compatible with all the DBMs Django supports, or I feel like I'm more at risk of trashing my database by misunderstanding a SQL command than I am at misunderstanding the Django API call, etc.

Take a look at [this](https://stackoverflow.com/questions/17375997/django-inner-join-queryset) and [this](https://stackoverflow.com/questions/42984676/django-equivalent-for-sql-query-using-inner-join-clause). links to similar questions. — noes1s, Oct 17 '17 at 21:18
@noes1s Your first link asks how to inner join from A to B and then inner join from B to C. Your second link asks how to mark only particular Django model fields for non-deferred loading. Neither of these touches what I'm asking for. I want to join from A to B twice: same row in A, different rows in B. — Isaac Sutherland, Oct 18 '17 at 00:21
Yes, I've edited my question to clarify that that is what I am doing now. I would just prefer to use a higher-level Django API call if one is available. — Isaac Sutherland, Oct 20 '17 at 13:31

noes1s · Answer 1 · 2017-10-18T21:26:03.733

Try Performing raw SQL queries in django. As well as select related in raw request.

prefetch on raw query:

from django.db.models.query import prefetch_related_objects
raw_queryset = list(raw_queryset) 
prefetch_related_objects(raw_queryset, ['a_related_lookup',
'another_related_lookup', ...])

Your example:

from django.db.models.query import prefetch_related_objects
raw_DataPacket = list(DataPacket.objects.raw)
pp = prefetch_related_objects(raw_DataPacket, ['field_set'])

Example of prefetch_related with Raw Queryset:

models:

class Country:
    name = CharField()
class City:
    country = models.ForeignKey(Country)
    name = models.CharField()

prefetch_related:

from django.db.models.query import prefetch_related_objects
#raw querysets do not have len()
#thats why we need to evaluate them to list
cities = list(City.objects.raw("select * from city inner join country on city.country_id = country.id where name = 'london'"))
prefetch_related_objects(cities, ['country'])

Answer provided from information from these sources: djangoproject - performing raw queries | Related Stackoverflow Question | Google docs question

Again, I'm not sure how I would use this to select exactly two rows from table B for each row from table A. I've updated my question to show how I currently do this using a database cursor and raw SQL. — Isaac Sutherland, Oct 20 '17 at 13:35

Django: Single query with multiple joins on the same one-to-many relationship

1 Answers1