django: select_related with entry_set

Question

Should entry_set be cached with select_related? My DB is still getting calls even after I use select_related. The pertinent sections

class Alias(models.Model):
    achievements = models.ManyToManyField('Achievement', through='Achiever')

    def points(self) :
        points = 0
        for a in self.achiever_set.all() :
            points += a.achievement.points * a.count
        return points

class Achievement(models.Model):
    name = models.CharField(max_length=100)
    points = models.IntegerField(default=1)

class Achiever(models.Model):
    achievement = models.ForeignKey(Achievement)
    alias = models.ForeignKey(Alias)
    count = models.IntegerField(default=1)

aliases = Alias.objects.all().select_related()
for alias in aliases :
    print "points : %s" % alias.points()
    for a in alias.achiever_set.all()[:5] :
        print "%s x %d" % (a.achievement.name, a.count)

And I'm seeing a big join query at the start, and then individual calls for each achievement. Both for the points and for the name lookup.

Is this a bug, or am I doing something wrong?

Conium

Answer 1

Select_related() doesn't work with manytomanyfields. At the moment, this is something that is not planned, but might be a future feature. See http://code.djangoproject.com/ticket/6432

In this case, if you want to make a single query you got two options 1) Make your own SQL, probably won't be pretty or fast. 2) You could also query on the model with the foreignkey. You would be able to use select_related in that case. You stil won't be able to access the modelname_set but with some formatting you would be able to vet the data you need in a single query. None of the options are ideal, but you could get it working at a deacent speed aswell.

Hopeh

Bummer. How would you recommend I do that query? SQL by hand? – Conium

For anyone reading, a select_related() equivalent for M2M and reverse FK is prefetch_related() and was added in 1.4. – Harker

Answer 2

With Django 1.4 you can use prefetch_related which will work for ManyToMany relations:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related

Basketball

Answer 3

In Django 1.3 You can use Queryset.values() and do something like:

Alias.objects[.filter().exclude() etc.].values('achievements__name', 'achievement__points')

Only drwaback is that You get QuerySetList instead of QuerySet. But this can be simply overcome by passing all necessary fields into values() - You have to change Your perception ;)

This can save you few dosen of queries...

Details can be found here in django docs: http://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.values

Endow