djangodjango-querysetdjango-ormdjango-aggregation

Django- Group by and Count by unique together


I have following models:

class Post(models.Model):
    title = models.CharField(max_length=30)

class PostView(models.Model):
    post = models.ForeignKey(Post, related_name='views', on_delete=models.CASCADE)
    user = models.ForeignKey(get_user_model(), related_name='my_views')
    created = models.DateTimeField(auto_now_add=True)

I want to get post views count, grouped by hour of day and unique.
for example if a user sees a post at 10 AM 20 times, just one time should be calculated. I get the posts in hours by views (not unique views) as following:

from django.db.models.functions import TruncHour
from django.db.models import Count

qs = PostView.objects.all().annotate(
        hour=TruncHour('created')
    ).values(
        'hour'
    ).annotate(
        c=Count('id')
    ).values('hour', 'c')

above code will calculate all views as total views. I want to get unique views by user_id and hour and post_id together. Is it possible to do that with ORM?


Solution

  • You can do this,

    from django.db.models import Count
    
    result = PostView.objects.values(
        "created__hour",
        "post",
        "user"
    ).annotate(count=Count("id"))
    
    print(list(result))
    
    # Result
    # [{'created__hour': 17, 'post': 1, 'user': 1, 'count': 4}, {'created__hour': 17, 'post': 2, 'user': 1, 'count': 3}]