Prefetch related django
django, django-queryset, python
Solution
From here https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related
select_related works by creating an SQL join and including the fields of the related object in the SELECT statement. For this reason, select_related gets the related objects in the same database query. However, to avoid the much larger result set that would result from joining across a ‘many’ relationship, select_related is limited to single-valued relationships - foreign key and one-to-one.
prefetch_related, on the other hand, does a separate lookup for each relationship, and does the ‘joining’ in Python.
update for comment:
it is better to place filter first here (order in django can affect on results):
interventionPreventivesVisits = InterventionPreventiveVisit.objects.filter(
date__range=(self.weekDays[0], self.weekDays[len(self.weekDays)-1])
).select_related("housing"
).prefetch_related("housing__tenants")
Problem
I'm developping an application written in Django and I have some problems to do a correct request using select_related and prefetch_related I have three models : ``` class Intervention(BaseModel): date = DateField() housing = ForeignKey('contract.Housing', related_name='interventions') class Housing(BaseModel): address = CharField(max_length=CHAR_FIELD_LENGTH) class Tenant(BaseModel): name = CharField(max_length=CHAR_FIELD_LENGTH) phone = CharField(max_length=CHAR_FIELD_LENGTH, blank=True, null=True) housing = ForeignKey(Housing, related_name='tenants') ``` I am requesting on the model Interventions, if I want to have access to the housing informations, I just have to use select_related : ``` Interventions.object.select_related("housing").filter(...) ``` But I don't know how to access to the tenants using prefetch_related : ``` Interventions.object.select_related("housing").prefetch_related("housing__tenants") ``` doesn't seems to work, because it makes a query everytime I try to access to the tenants list. Is there a way to access to the tenant list, and best, to do a filter on I (like first Tenant found which has no name). Thanks for you answers. Algelos *Edit : Here is some code : * I'm requesting like I said : ``` interventionPreventivesVisits = InterventionPreventiveVisit.objects.select_related("housing").prefetch_related("housing__tenants").filter(date__range=(self.weekDays[0], self.weekDays[len(self.weekDays)-1])) ``` self.weekDays is a table of days, to display the interventions in a calendar. And then, I want to display the tenant which has no name : In my template, I loop through the interventions : ``` {%for inter in interventions %} {%if day == inter.date %} {{ inter | get_schedule_html_formated | safe}} {%endif%} {% endfor %} ``` And I have a templateTag to display the HTML : ``` def get_schedule_html_formated(intervention): housingTenant = None for tenant in intervention.housing.tenants.all(): # Here it does a query if tenant.name is not None: housingTenant = tenant .... ``` and then I write and return my html I'm searching a way to set housingTenant without doing a new query. Is that better :) ?