Count number of distinct records by date in Django

Question

following this question: Count number of records by date in Django

class Review(models.Model):
    venue = models.ForeignKey(Venue, db_index=True)
    review = models.TextField()  
    datetime_visited = models.DateTimeField(default=datetime.now)

It is true that the following line solves the problem of count number of records by date:

Review.objects.filter
    .extra({'date_visited' : "date(datetime_visisted)"})
    .values('date_visited')
    .annotate(visited_count=Count('id'))

However, say I would like to have a distinct count, that is, I would like to avoid Review objects from the same id on the same day, what can I do?

I tried:

Review.objects.filter.
    .extra({'date_visited': "date(datetime_visited)"})
    .values('date_visited', 'id')
    .distinct()
    .annotate(Count('id'))

but it seems not working

score 0 · Accepted Answer · answered Jan 21 '15 at 15:09

Your problem is that you're including id in your values(), which is making all records unique, defeating distinct(). Try this instead:

Review.objects.filter.
    .extra({'date_visited': "date(datetime_visited)"})
    .values('date_visited')
    .distinct()
    .annotate(Count('date_visited'))

Count number of distinct records by date in Django

1 Answers1