Django ORM calculate all Members of Group

Question

I am trying to annotate all my groups by the number of Users, that apply to a certain condition. In this case I want to get a number of users, that have related Offer model to the Task in the Group.

Users can create Offers to the Task. One Task can be related to one Group.

So as the result of annotating Groups I want something like this

| id |  name  | runners_num |
| -- | ------ | ----------- |
| 1  | name1  |      3      |
| 2  | name2  |      5      |

With a query below I can get this count as a dictionary

runners = User.objects.filter(
        offer__tack__group_id=1
).distinct(

).values(
    'id'
).aggregate(
    count=Count('id')
)

output: {'count': 5}

But I can't figure out how to do this with OuterRef clause

runners = User.objects.filter(
        offer__task__group_id=OuterRef('id')
).distinct().values('id')
groups = Group.objects.annotate(
    runners_num=Count(Subquery(runners))
)

It ended up with this wrong query

SELECT 
  "groups"."id", 
  "groups"."name", 
  COUNT(
    (
      SELECT 
        DISTINCT U0."id" 
      FROM 
        "users" U0 
        INNER JOIN "offers" U1 ON (U0."id" = U1."runner_id") 
        INNER JOIN "tasks" U2 ON (U1."task_id" = U2."id") 
      WHERE 
        U2."group_id" = ("groups"."id")
    )
  ) AS "runners_num" 
FROM 
  "groups" 
GROUP BY 
  "groups"."id" 
LIMIT 
  21

My models

class Task(models.Model):
    tasker = models.ForeignKey(
        "user.User", null=True, blank=True, on_delete=models.SET_NULL, related_name="tasker"
    runner = models.ForeignKey(
        "user.User", null=True, blank=True, on_delete=models.SET_NULL, related_name="runner",
    )
    group = models.ForeignKey(
        "group.Group",
        on_delete=models.CASCADE
    )
class Offer(models.Model):
    task = models.ForeignKey("task.Task", on_delete=models.CASCADE)
    runner = models.ForeignKey("user.User", null=True, blank=True, on_delete=models.SET_NULL)
class Group(model.Model):
    name = models.CharField(max_length=100)
class GroupMembers(models.Model):
    group = models.ForeignKey("group.Group", on_delete=models.CASCADE)
    member = models.ForeignKey("user.User", null=True, blank=True, on_delete=models.SET_NULL)

EDIT

I have conditions where my Users get filtered. In my case I want count only Users that have more than 3 Offers that apply to conditions. So probably I can't get rid of Subquery statement and OuterRef field.

runners = User.objects.filter(
    offer__task__group_id=OuterRef('id')
).distinct(
).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,
        ),
        offer__runner_id=F('id'),
    )
).filter(
    offer_num__gte=3
).values('id')

It is working fine if I replace OuterRef('id') with just a int Group number. But I don't know the proper solution how to count on this QuerySet. Something like

runners_num = Count(Subquery(runners))

And after that I get

django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression

Answer 1

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'
)

Answer 2

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