[Answered ]-Django uniqe by date queryset filtering

by

2👍

✅

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:

visits.count()

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.

👤tdsymonds

Leave a comment