0

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 posts ordered by number of unique views. I get the posts ordered by views by following code:

filters = {
    'created__date__gte': datetime.datetime(year=2020, month=1, day=1),
    'created__date__lte': datetime.datetime(year=2021, month=1, day=1),
}

qs = Post.objects.all().annotate(
    total_views=Count('views', filter=Q(**filters))
).order_by('-total_views')

above code will calculate all views as total_views. I want to get unique views by user. Is it possible to do that with ORM?

msln
  • 1,318
  • 2
  • 19
  • 38

1 Answers1

0

Instead of counting views, you can count views__user directly and apply a distinct=True on this aggregation:

qs = Post.objects.all().annotate(
    total_views=Count('views__user', distinct=True, filter=Q(**filters))
).order_by('-total_views')
GwynBleidD
  • 20,081
  • 5
  • 46
  • 77
  • what about unique views by hours of day? `qs = PostView.objects.filter(**filters ).annotate(hour=TruncHour('created')).values('hour').annotate(c=Count('user_id', distinct=True)).values('hour', 'c')`. this is not working. – msln May 11 '21 at 07:08