How to reduce queries in django model has_relation method?

Question

Here are two example Django models. Pay special attention to the has_pet method.

class Person(models.Model):
    name = models.CharField(max_length=255)

    def has_pet(self):
        return bool(self.pets.all().only('id'))

class Pet(models.Model):
    name = models.CharField(max_length=255)
    owner = models.ForeignKey(Person, blank=True, null=True, related_name="pets")

The problem here is that the has_pet method always generates a query. If you do something like this.

p = Person.objects.get(id=1)
if p.has_pet():
    ...

Then you will actually be doing an extra query just to check if one person has a pet. That is a big problem if you have to check multiple people. It will also generate queries if used in templates like this.

{% for person in persons %}
    {% if person.has_pet %}
        {{ person.name }} owns a pet
    {% else %}
        {{ person.name }} is petless
    {% endif %}
{% endfor %}

This example will actually perform an extra query for every person in the persons queryset while it is rendering the template.

Is there a way to do this with just one query, or at least doing less than one extra query per person? Maybe there is another way to design this to avoid the problem altogether.

I thought of adding a BooleanField to Person, and having that field be updated whenever a pet is saved or deleted. Is that really the right way to go?

Also, I already have memcached setup properly, so those queries only happen if the results are not already cached. I'm looking to remove the queries in the first place for even greater optimization.

Answer 1

If you want a list of all the people with pets you can do that in a single query:

Person.objects.exclude(pets=None)

Sounds like you want to iterate over a single list of people, using annotations would probably make sense:

for person in Person.objects.annotate(has_pet=Count('pets')):
     if person.has_pet: # if has_pet is > 0 this is True, no extra query

It'd be nice if Django had an Exists aggregate but it doesn't, and I don't know how difficult it would be to add one. You should profile of course, and figure out if this works for you.

Personally, I'd probably just store has_pets as a boolean on the model, it's probably the most efficient approach.