[Answered ]-Django ORM calculate all Members of Group

by

0👍

✅

So my final solution is to carry out User’s subquery and only then aggregate by Count function.

users_runners = User.objects.filter(
    offer__task__group_id=OuterRef(OuterRef('id'))
).annotate(
    offer_num=Count(
        'offer',
        filter=
        Q(
            offer__task__completion_time__isnull=False,
            offer__task__completion_time__gte=timezone.now() - timedelta(hours=24),
        ) |
        Q(
            offer__task__status__in=(
                TaskStatus.ACTIVE,
                TaskStatus.ACCEPTED,
                TaskStatus.IN_PROGRESS,
                TaskStatus.WAITING_REVIEW
            ),
            offer__task__is_active=True,
        )
    )
).filter(
    offer_num__gte=3
).values(
    'id'
).distinct(

)

runners = User.objects.filter(
    id__in=users_runners,
).annotate(
    count=Func(
        F('id'),
        function='Count'
    )
).values(
    'count'
)

groups = Group.objects.annotate(
    runners_num=runners
).order_by(
    'id'
)
👤Ayudesee

1👍

Annotate the number of users who have at least one offer for each group’s task, correct?
I believe this should do:

Group.objects.annotate(
    runners_count=Count(
        'task__offer__user', distinct=True
    )
).values('id', 'runners_count')

Count() has some neat kwargs you can leverage like that: https://docs.djangoproject.com/en/3.2/ref/models/querysets/#django.db.models.Count

👤Clepsyd

Leave a comment