Optimize django query to pull foreign key and django-taggit relationship

Question

I have a todo model defined below:

class Action(models.Model):
    name = models.CharField("Action Name", max_length=200, unique = True)

    complete = models.BooleanField(default=False, verbose_name="Complete?")

    reoccurance = models.ForeignKey(Reoccurance, blank=True, null=True, verbose_name="Reoccurance")
    notes = models.TextField("Notes", blank=True)

    tags = TaggableManager()

class Reoccurance(models.Model):
    label = models.CharField("Label", max_length=50, unique = True)
    days = models.IntegerField("Days")

I want to list all of the actions that are incomplete:

actions = Action.objects.filter(complete=False)

My template loops of the actions list:

{% for action in actions %}
    <p>{{ action }}</p>
    {% if action.reoccurance %}
        <p>{{ action.reoccurance }}</p>
    {% endif %}
    {% for tag in action.tags.all %}
        <span>{{ tag }}</span>{% if not forloop.last %}, {% endif %}
    {% endfor %}
{% endfor %}

Using django-debug-toolbar, I see that for every action, I'm hitting the database on {% if action.reoccurance %} and {% for tag in action.tags.all %}.

Is there a better way to write my query so that the database isn't pinged for every iteration of the loop? I think it has something to do with select_related, but I'm not sure what to do about django-taggit.

Update I got part of my answer. select_related does work, but I had to specify reoccurance, probably because I can't use it for tags:

actions = Action.objects.select_related('reoccurance').filter(complete=False)

The problem still remains that I hit the database for every "action.tags.all" in the template loop. Is it possible to use some sort of prefetch on django-taggit?

Pearce

Answer 1

It's possible to use prefetch_related to retrieve the tags, but you need to sidestep around the 'tags' property, since - as jdi says - this is a custom manager rather than a true relation. Instead, you can do:

actions = Action.objects.select_related('reoccurance').filter(complete=False)\ .prefetch_related('tagged_items__tag')

Unfortunately, action.tags.all in your template code will not make use of the prefetch, and will end up doing its own query - so you need to take the rather hacky step of bypassing the 'tags' manager there too:

{% for tagged_item in action.tagged_items.all %}
    <span>{{ tagged_item.tag }}</span>{% if not forloop.last %}, {% endif %}
{% endfor %}

(Ed.: if you're getting "'QuerySet' object has no attribute 'prefetch_related'", that suggests that you're on a version of Django below 1.4, where prefetch_related isn't available.)

Hortense

Answer 2

The problem is that tags is not a field, but a custom manager, which lives at the class-level and simply does queries.

I am not sure if this will work on custom managers, as it is meant for many-to-many fields and the like that produce similar query sets. But if you are using django 1.4 you can try the prefetch_related. It will do one more query that batches out the relations and caches them.

Disclaimer again: I don't know if this works for managers

actions = Action.objects.select_related('reoccurance').filter(complete=False)\
                .prefetch_related('tags')

Barbel

thanks for the input. Disclaimer was probably right: 'QuerySet' object has no attribute 'prefetch_related'. Should I just accept that I will have a database hit for ever action in actions? – Pearce

I cant be 100% sure as i have never looked at that app, but my gut tells me that is can only be done on fields. Now, you could probably manually query all the tags by a list of ids in one query and use that result instead of thr manager on every object in a loop. – Barbel