When should I use annotations vs. model methods in Django?

user_7303434 0
Viewed 681

I have a situation where I need to add additional data to the objects within a Queryset, and I have troubles understanding when it is really beneficial to perform queries that have annotations, over just implementing custom methods on the model class.

As a generic example:

This:

 MyModel.objects
        .filter(
            some_field=some_value
        )
        .annotate(
            has_something=Exists(
                Something.objects.filter(
                    whatever=OuterRef('pk'),
                    start_date__gte=SIX_MONTHS_AGO
                )
            ),
            something_else= ... # Some complex query
        )

Versus this:

class MyModel(models.Model):
    some_field = CharField()

    def has_something(self):
        return (self.something_set
                    .filter(start_date__gte=SIX_MONTHS_AGO)
                    .count() > 0)

    def something_else(self):
        ... # Make some other queries


class Something(models.Model):
    whatever = models.ForeignKey(MyModel)
    start_date = models.DateField()

So in both cases I can do something like this wherever I need to (for example, in the templates):

{% for obj in my_model_queryset %}
    {{ obj.has_something }}
{% endfor %}

As far as I know, when doing annotations all the work is done by the database engine and everything can be done in a single database hit (I think?). On the other hand, when implementing a method on the model class, additional queries might be performed if you need to get reverse lookups, or data from a foreign model.

I just want to ask, is there any rule of thumb, or any guidance that you all consider for when to use one approach over the another? Especially if the annotations get really complex (e.g, using Q objects, Subqueries, GroupConcat, and so on)

Thanks.

django-orm django-models django python
1 Answers

here an exemple :

model_datas = Model.objects.filter(
    field_date__date__range=[date_from, date_to]
).order_by(
    'field_date'
)


# each line will perform a query here :(
for model in model_datas:
    writer.writerow([
        model.user.id,
        model.user.email,
        model.business_provider.encode('utf-8'),
        model.field_date.date()
    ])

This will perform 1 query for all Model + 1 query with a join on User for each model in loop

But you can do it in only one query :

csv_datas = Model.objects.filter(
    field_date__date__range=[date_from, date_to]
).order_by(
    'field_date'
).annotate(
    user_id=F('model__user_id'),
    user_email=F('model__user__email'),
    business_provider=F('model__provider_name'),
    field_date=F('model__field_date'),
).values(
    'user_id',
    'user_email',
    'business_provider',
    'field_date'
)

for data in csv_datas:
    writer.writerow([
        data["user_id"],
        data["user_email"],
        data["business_provider"].encode('utf-8'),
        data["field_date"].date()
    ])

Here only one query is perform, that is the thing to do.

In Django never do a loop with subquery performed, it's really bad for performance.

with F and Q you can do pretty complex stuff, what i like to do when it's really complex, is to split in multiple query and just use :

query_1_ids = Model.objects.filter(XXXX).values_list('pk', flat=True)

query2 = Model2.objects.filter(model__pk__in=query_1_ids)

That my rule of thumb, but if this didn't fit, i do a python process for aggregating in a set or dict what i need (but it's really rare to be here)

user_50733770
Related