Goran Goran - 10 months ago 67
Python Question

Django uniqe by date queryset filtering

I have a queryset which represent visits and now I need to filter unique visits by day for all week.

class Visit(models.Model):
user = models.ForeignKey(User)
created_at = models.DateTimeField(auto_now_add=True)

week = timezone.now().date() - timedelta(days=7)
visits = Visit.objects.filter(created_at__gte=week)

How can I filter visits queryset to get unique visits per day for one week range. So if user comes 4 times in a single day, it is one unique visit. Expected result is integer which represent unique visits for the last week.

Answer Source

The below will create a new field in the queryset called visit_date: that is the date (stripped of the time) from the created_at attribute of your model. Using the Count function, we can then group the queryset by the user and date, and count the number of visits for each date:

from django.db.models import Count

week = timezone.now().date() - timedelta(days=7)
visits = Visit.objects.filter(created_at__gte=week)).extra({'visit_date' : 'date(created_at)'}).values('user', 'visit_date').annotate(visits_on_date=Count('pk'))

The visits queryset will looks something along the lines of:

    {'user': 1, 'visit_date': datetime.date(2016, 11, 4), 'visits_on_date': 2},
    {'user': 2, 'visit_date': datetime.date(2016, 11, 4), 'visits_on_date': 5},
    {'user': 1, 'visit_date': datetime.date(2016, 11, 3), 'visits_on_date': 7},
    {'user': 2, 'visit_date': datetime.date(2016, 11, 3), 'visits_on_date': 9},
    {'user': 1, 'visit_date': datetime.date(2016, 11, 2), 'visits_on_date': 5}

Each item in the queryset relates to a unique visit for that date for a particular user. You can determine the number of unique visits by:


Which in this instance would be 5.

You can also use this to determine the number of visits a particular user makes, by looking at the visits_on_date field in the queryset.