Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django select related in raw request

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.

like image 640
Andrew Avatar asked Feb 17 '12 12:02

Andrew


People also ask

What is the use of SELECT related in Django?

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.

How do I create a raw query in Django?

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!

Why are QuerySets considered lazy?

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.

Does Django ORM support subquery?

¶ Django allows using SQL subqueries.


2 Answers

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.

like image 53
Todor Avatar answered Oct 17 '22 09:10

Todor


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.

like image 45
willy Avatar answered Oct 17 '22 08:10

willy