How to reduce queries in django model has
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.
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.
上一篇: 如何从C ++ DLL动态加载C#DLL
下一篇: 如何减少django模型中的查询