[Solved]-Get foreign key objects in a single query



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.

๐Ÿ‘คDaniel Roseman


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


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]
๐Ÿ‘คOfri Raviv


from django.db.models import OuterRef, Subquery

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



Leave a comment