How to make "manual" select_related imitation to avoid undesirable DB hits?
we have:
class Country:
name = CharField()
class City:
country = models.ForeignKey(Country)
name = models.CharField()
cities = City.objects.raw("select * from city inner join country on city.country_id = country.id where name = 'london'")
#this will hill hit DB
print cities[0].country.name
How to tell django that related models are already fetched.
select_related obtains all data at one time through multi-table join Association query and improves performance by reducing the number of database queries. It uses JOIN statements of SQL to optimize and improve performance by reducing the number of SQL queries.
Django gives you two ways of performing raw SQL queries: you can use Manager. raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly. Explore the ORM before using raw SQL!
This is because a Django QuerySet is a lazy object. It contains all of the information it needs to populate itself from the database, but will not actually do so until the information is needed.
¶ Django allows using SQL subqueries.
A solution with prefetch_related
(this means that two queries will be made, 1 for the cities
and 1 for the countries
) taken from django-users which is not part of the public API but is working on Django 1.7
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'])
UPDATE
Now in Django 1.10 prefetch_related_objects
is part of the public API.
Not sure if you still need this, but I solved it starting with Alasdair's answer. You want to use the info from the query to build the model or it'll still fire additional queries when you try to access the foreign key field. So in your case, you'd want:
cities = list(City.objects.raw("""
SELECT
city.*, country.name as countryName
FROM
cities INNER JOIN country ON city.country_id = country.id
WHERE
city.name = 'LONDON"""))
for city in cities:
city.country = Country(name=city.countryName)
The line that assigns the country doesn't hit the database, it's just creating a model. Then after that, when you access city.country
it won't fire another database query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With