stupid SQL

[g00fy@…]

Django orm does not follow reverse relations. Why not?
I present example how this could be done:

#models:
from django.db import models

class Book(models.Model):
    ISBN = models.IntegerField()

class Translation(models.Model):
    title = models.CharField(max_length=32)
    book = models.ForeignKey("Book")
    language= models.CharField(max_length=4)
#views:
def index(request):
    books = Book.objects.filter(translation__language="en",translation__title="1")
    output = ', '.join([obj.translation_set.all()[0].title for obj in books])
    return HttpResponse(output)


#what django does:SQL
SELECT "test3_book"."id", "test3_book"."ISBN" FROM "test3_book" INNER JOIN "test3_translation" ON ("test3_book"."id" = "test3_translation"."book_id") WHERE ("test3_translation"."language" <> 'bz'  AND "test3_translation"."title" <> 'fdsfsd' )

SELECT "test3_translation"."id", "test3_translation"."title", "test3_translation"."book_id", "test3_translation"."language" FROM "test3_translation" WHERE "test3_translation"."book_id" = 1 LIMIT 1
# so if we have 100 objects it will do 100 additional queries


#change proposed :
#this would be just fine (just added Translation fields):

SELECT 
"test3_book"."id", 
"test3_book"."ISBN",
"test3_translation"."title",   #this was added 
"test3_translation"."language" #this was added
FROM "test3_book"
INNER JOIN "test3_translation" 
ON ("test3_book"."id" = "test3_translation"."book_id")
WHERE ("test3_translation"."language" <> 'bz'  AND "test3_translation"."title" <> 'fdsfsd' )

this means that if I will get 100 models, djanog will hit db 100 more times!
this can be avoided by adding the extra fields from related model to SELECT

Note that this is only example, what if Book has more than 1 related object, fetching by Translation makes no sense.

[peritus <peritus@…>]

You want to use select_related():

​http://docs.djangoproject.com/en/dev/ref/models/querysets/#id3

Doesn't work for ManyToMany fields, though.

[Malcolm Tredinnick]

This rapidly gets very, very difficult once you have more than one related model, because adding all the fields to select leads to selecting N * M rows, if there are N rows in the first related table and M in the second (instead of N + M + 1 rows, which is the ideal). The select_related() processing is already fairly complex, so this isn't a trivial change. When somebody writes a solid patch to improve it, we'll certainly accept it.

There's already a ticket open to add similar "follow reverse relations" for select_related() and #7270 handles the special case of reverse OneToOneFields (which is the one case where multiple rows won't be returned).

It isn't stupid SQL, it's a conscious decision to avoid a very bad performance overhead. The same effect can be achieved in almost all cases (including the above one) by turning the second queryset around to be a query on the Translation table using the book ids that you already know, so it's two queries not 101.

"Wontfixing" this ticket, since the behaviour is documented and intentional. If somebody writes a patch to make things better, it can be attached to a ticket at that time.

[(none)]

Milestone post-1.0 deleted