Avoiding multiple queries for multiple counts

Question

I'm trying to figure out a good way to get a few analytics counts from my DB without doing a bunch of queries and somehow doing one

What I have right now is a function that returns counts

def get_counts(self):
    return {
        'item_one_counts'  : self.items_one.count(),
        'item_two_counts'  : self.items_two.count(),
        'item_three_count' : self.items_three.count(),
    }

etc.

I know I can do this with a raw query that does a SELECT as count1,2,3 FROM table X

Is there a more django-y way to do this?

Did you check my solution?

Danyal Sandeelo
– Danyal Sandeelo

2014-12-18 11:06:30 +00:00
Commented Dec 18, 2014 at 11:06 — Danyal Sandeelo
– Danyal Sandeelo, Commented Dec 18, 2014 at 11:06

knbk · Accepted Answer · 2014-12-18 13:50:29Z

You're a tad late if you want to get the counts in an instance method. The easiest way to optimize this is by using annotations in the initial query:

obj = MyModel.objects.annotate(item_one_count=Count('items_one')) \
             .annotate(item_two_count=Count('items_two')) \
             .annotate(item_three_count=Count('items_three')) \
             .get(...)

Another good optimization is to cache the results, e.g.:

MyModel(models.Model):
    def get_item_one_count(self):
        if not hasattr(self, '_item_one_count'):
            self._item_one_count = self.items_one.count()
        return self._item_one_count

    ...

    def get_counts(self):
        return {
                'item_one_counts'  : self.get_item_one_count(),
                'item_two_counts'  : self.get_item_two_count(),
                'item_three_count' : self.get_item_three_count(),
        }

Combine these methods (i.e. .annotate(_item_one_count=Count('items_one'))), and you can optimize the counts into a single query when you have control over the query, while having fallback method in case you can't annotate the results.

Another option is to perform the annotation in your model manager, but you will no longer have fine-grained control over the queries.

Collectives™ on Stack Overflow

Avoiding multiple queries for multiple counts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related