CODEWITHSUNDEEP
pythondjangodjango-queryset
iMom0
iMom0

Reputation: 12931

Use django count or values_list counter, which is better?

I write a view for exporting data, my model is like this:

class Event(models.Model):
    KIND_CHOICES = (('doing', 'doing'),
                    ('done', 'done'),
                    ('cancel', 'cancel'))
    created_at = models.DateTimeField(auto_now_add=True)
    created_by = models.ForeignKey('auth.User')
    kind = models.CharField(max_length=20, choices=KIND_CHOICES)

Event is of one kind in three kinds, every user may has 3~10 events every month, firstly I query events of this month:

events_this_month = Event.objects.filter(created_at__year=2013,
                                         created_at__month=5)

then find all the users:

users = User.objects.all()

I export data like this:

for user in users:
    # 1000 users with 5 events each
    user_events = events_this_month.filter(created_by=user)
    doing_count = user_events.filter(kind='doing').count()
    done_count = user_events.filter(kind='done').count()
    cancel_count = user.events.filter(kind='cancel').count()

    append_to_csv([user.username, doing_count, done_count, cancel_count])

Then I tried using collections.Counter, I think this will cut down count SQL times(actually it decreases to 1200 from 3000+):

for user in users:
    user_events = events_this_month.filter(created_by=user)
    counter = Counter(user_events.values_list('kind', flat=True))
    doing_count = counter['doing']
    done_count = counter['done']
    cancel_count = counter['cancel']

    ...

Which way is better?

Is where a more idiomatic way to count data like this more effciently?

Upvotes: 4

Views: 1260

Answers (1)

Aamir Rind
Aamir Rind

Reputation: 39689

This is not tested but the idea is to group by user and then group by kind:

from django.db.models import Count

events_this_month = Event.objects.values('created_by', 'kind') \
                         .filter(created_at__year=2013, created_at__month=5) \
                         .annotate(cc=Count('kind'))

Let me know if this works as i have not tested this.

Upvotes: 3

Related Questions