Get foreign key objects in one request - Django

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 entries, all of which may or may not have links to ModelB

Now say I want to get a list of each ModelA record along with ModelB data

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 triggers a query for each instance of i. Thus, 101 queries are executed.

Is there a way to run all this in just one request. Or at least less than 101 queries.

I tried to insert ModelA.objects.select_related().all() , but it showed no effect.

thanks

Answer 1

As Ofri says, select_related only works with forward relationships, not backward ones.

There is no built-in way to automatically track feedback in Django, but see my blog post for doing it reasonably efficiently. The main idea is to immediately get all the related objects for each element, and then link them manually with your related element - so you can do this in 2 queries, not in n + 1.

Answer 2

Django ORM is good, but some things are best done manually. You can import the connection cursor and execute raw sql in a single query.

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

your query should look like this (for MySQL)

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

Answer 3

The reason .select_related () is not working is because .select_related () is used to use foreign keys. ModelA does not have a foreign key to ModelB. Its ModelB, which has a foreign key for ModelA. (therefore, a ModelA instance can have multiple ModelB instances associated with it).

You can use this to do this in 2 queries and some 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]