Get foreign key objects in a single query

Question

I have 2 models in my Django code:

class ModelA(models.Model):
    name = models.CharField(max_length=255)
    description = models.CharField(max_length=255)
    created_by = models.ForeignKey(User)

class ModelB(models.Model):
    category = models.CharField(max_length=255)
    modela_link = models.ForeignKey(ModelA, 'modelb_link')
    functions = models.CharField(max_length=255)
    created_by = models.ForeignKey(User)

Say ModelA has 100 records, all of which may or may not have links to ModelB

Now say I want to get a list of every ModelA record along with the data from ModelB

I would do:

list_a = ModelA.objects.all()

Then to get the data for ModelB I would have to do

for i in list_a:
    i.additional_data = i.modelb_link.all()

However, this runs a query on every instance of i. Thus making 101 queries to run.

Is there any way of running this all in just 1 query? Or at least less than the 101 queries.

I've tried putting in ModelA.objects.select_related().all() but this didn't seem to have any effect.

score 7 · Accepted Answer · answered May 14 '10 at 10:09

As Ofri says, select_related only works on forwards relations, not reverse ones.

There's no built-in way to automatically follow reverse relations in Django, but see my blog post for a technique to do it reasonably efficiently. The basic idea is to get all the related objects for every item at once, then associate them manually with their related item - so you can do it in 2 queries rather than n+1.

Shamanu4 · Answer 2 · 2010-05-14T09:33:05.100

1

Django ORM is a good thing but some some things is better to do manually. You may import connection cursor and execute raw sql in single query.

from django.db import connection
cur=connection.cursor()
cur.execute(query)
rows = cur.fetchall()

your query should look like (for MySQL)

SELECT * FROM appname_modela INNER JOIN appname_modelb ON appname_modela.id=appname_modelb.modela_link_id

edited May 14 '10 at 09:33

answered May 14 '10 at 09:24

Shamanu4

5,296
2
27
31

1

I know that when using raw sql you don't get an object returned so do you know of any way to convert the results into a Django object – John May 14 '10 at 09:47
currently you cant create raw sql results into django queryset – Arpit Singh Aug 20 '19 at 11:57

score 1 · Answer 3 · answered May 14 '10 at 10:02

The reason .select_related() didn't work, is that .select_related() is used to follow foreign keys. Your ModelA doesn't have a foreign key to ModelB. Its ModelB that has a foreign key to ModelA. (so a ModelA instance can have multiple ModelB instances related to it).

You could use this to do it in 2 queries, and a bit of python code:

list_b = ModelB.objects.all()
list_a = ModelA.objects.all()
for a in list_a:
    a.additional_data = [b for b in list_b if b.modela_link_id==a.id]

score 0 · Answer 4 · answered Oct 28 '21 at 19:10

0

from django.db.models import OuterRef, Subquery

newest = ModelB.objects.filter(modela_link=OuterRef('pk'))
ModelA.objects.annotate(newest=Subquery(newest))

https://docs.djangoproject.com/en/3.2/ref/models/expressions/#subquery-expressions

answered Oct 28 '21 at 19:10

ykrop

56
1
2

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/30204729) – Oct 28 '21 at 22:30

Get foreign key objects in a single query

4 Answers4

Linked