[Answered ]-Django – Count and filter in queryset

by

1👍

✅

you can do this

week = timezone.now() - timezone.timedelta(days=7)
my_username = "my_username"

lst_week = User.objects
            .annotate(count=Count('commit_creator', filter=Q(commit_creator__date__gte=week)))
            .filter(~Q(username=my_username), count__gte=1)
            .order_by('-count')
            .prefetch_related('commit_creator')
            .values('count', 'username')[:3]

explained :

# here you set time now less seven days
week = timezone.now() - timezone.timedelta(days=7)

# change with your username 
my_username = "my_username"

lst_week = User.objects
            # you count commit by creator and you count only when date is upper thans seven days
            .annotate(count=Count('commit_creator', filter=Q(commit_creator__date__gte=week)))
            # you remove your username and remove all user have not "commit"
            .filter(~Q(username=my_username), count__gte=1)
            # order reverse by count
            .order_by('-count')
            # optimzie for sql union
            .prefetch_related('commit_creator')
            # and get only count and username value and only 3
            .values('count', 'username')[:3]
👤rgermain

Leave a comment