Re: ORM only/defer calls do not work on cross-table relations

Hi John,

Use select_related [1] to tell Django to 'follow' the foreign key. Try
the following:

testmodels = models.ATestModel.objects.all().select_related('other').only('other__name')
print testmodels[0].other.name

Regards,
Alasdair

[1]:
https://docs.djangoproject.com/en/dev/ref/models/querysets/#select-related

On 27/09/11 21:49, John wrote:
> Hey,
>
> I'm trying to improve the performance of a Django app, and noticed
> that you can't seem to properly defer fields when making lookups with
> joins. To demonstrate, I set up a test project with the following
> models:
>
> class ATestModel(models.Model):
> other = models.ForeignKey('OtherModel')
>
> class OtherModel(models.Model):
> name = models.CharField(max_length=32)
>
> then, in the Django shell, I set up 10 'ATestModel's and two
> 'OtherModel's properly linked up, and ran the following:
>
>>>> from testmodule import models
>>>> testmodels = models.ATestModel.objects.all().only('other__name')
>>>> print testmodels[0].other.name
> Test One
>>>> from django.db import connection
>>>> print '\n\n'.join([x['sql'] for x in connection.queries])
> SELECT "testmodule_atestmodel"."id",
> "testmodule_atestmodel"."other_id" FROM "testmodule_atestmodel" LIMIT
> 1
>
> SELECT "testmodule_othermodel"."id", "testmodule_othermodel"."name"
> FROM "testmodule_othermodel" WHERE "testmodule_othermodel"."id" = 1
>
> I also re-ran that without the 'only' call:
>
>>>> testmodels = models.ATestModel.objects.all()
>>>> print testmodels[0].other.name
> Test One
>>>> print '\n\n'.join([x['sql'] for x in connection.queries[2:]])
> SELECT "testmodule_atestmodel"."id",
> "testmodule_atestmodel"."other_id" FROM "testmodule_atestmodel" LIMIT
> 1
>
> SELECT "testmodule_othermodel"."id", "testmodule_othermodel"."name"
> FROM "testmodule_othermodel" WHERE "testmodule_othermodel"."id" = 1
>
> The 'only' call does nothing, and two queries are made when one would
> have sufficed (SELECT testmodule_othermodel.name FROM
> testmodule_atestmodel LEFT JOIN testmodule_othermodel ON
> testmodule_atestmodel.id = testmodule_othermodule.id). This raises a
> huge performance penalty for highly normalized schemas and I would
> even go so far as to say it in fact makes it impossible to optimize
> queries with a decently normalized schema.
>
> My apologies if someone has brought this up before - I was unable to
> see anything about it in the django tickets or mailing lists.
>

--
Alasdair Nicol
Developer, MEMSET

mail: alasdair@memset.com
web: http://www.memset.com/

Memset Ltd., registration number 4504980. 25 Frederick Sanger Road, Guildford, Surrey, GU2 7YD, UK.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.