Django - Count and filter in queryset-CodePudding

i want to create a complex queryset , after many time witout sucess :( i ask your help,

is my models:

class Commit(odels.Model):
    date = models.DateTimeField(auto_now_add=True)
    created = models.BooleanField(default=False)
    creator = models.ForeignKey(
       get_user_model(),
       on_delete=models.SET_NULL,
       null=True,
       blank=True,
       related_name="%(class)s_creator"
    )
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')
    uuid = models.CharField(max_length=200)
    updated_fields = models.TextField(null=True, blank=True)

i want to count how many "commit" user have, remove user if user as no "commit" and commit lower than 7 days , sort by upper, and remove my account to this filter , how can to this ? thx

CodePudding user response：

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]