# Querysets, Aggregation and filtering their results using other querysets

**URL:** <https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044>\
**Category:** Using Django\
**Created:** [March 14, 2021, 8:55pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044 "2021-03-14T20:55:12Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![conor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/conor/32/3736_2.png) [@conor](https://forum.djangoproject.com/u/conor)\
**Post date:** [March 14, 2021, 8:55pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/1 "2021-03-14T20:55:13Z")

</div>

I’ve recently been given a new requirement which involves filtering objects based on some custom user permissions. The requirement is quite straightforward but I’ve come unstuck at the following:

A user interacts with **cases** and every **case** has a **group**

**Group**

```python
class Group(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    name = models.CharField(max_length=128, unique=True)

```

**Case**

```python
class Case(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4)
    title = models.CharField(max_length=128)
    group = models.ForeignKey(Group, related_name="cases", on_delete=models.CASCADE)

```

Based on some permissions, I filter the cases a user can see. This part is working as expected.

However, I have an endpoint which must list all groups in which there are cases a user can access along with the count of the total number of cases in each group to which a user can access.

As an example, let’s look at three users:

1. Julie has access to four cases in the group **birds**
2. Jenny has access to two cases in the group **birds**
3. Juliet has access to two cases in the group **birds** and three cases in the group **insects**

When presenting a summary of available groups, the user should see every group for which they have access to cases within that group, and the count of the cases to which they have access in that group. They should not see groups in which there are no cases to which they have access.

As an example of the desired result for each of the above users.

**Julie** : has four cases in birds, no cases in any other group

```json
[
 {
   "name": "birds",
   "case_count": 4
 } 
]

```

**Jenny** : has two cases in birds, no cases in any other group

```json
[
 {
   "name": "birds",
   "case_count": 2
 }
]

```

**Juliet** : has two cases in birds and three cases in insects

```json
[
 {
   "name": "birds",
   "case_count": 2,
 },
 {
   "name": "insects",
   "case_count": 3,
 } 
]

```

Until this permissions requirement was presented to me, I used to get all of the groups which had a case count greater than 0.

```python
        return Group.objects.annotate(
          case_count=Count("cases")
        ).filter(case_count__gt=0)

```

Now that I have to deal with the permissions, I first filter the user’s case queryset, and for **Juliet** it would look something like this:

```bash
Queryset = [
   <birds case 1>,
   <birds case 2>,
   <insects case 1>,
   <insects case 2>,
   <insects case 3>,
]

```

**Note** : It is a Django Queryset that I am returning and what I am representing above. It is not an array.

Using the data from the above queryset, I want to get the group of each case, return only a single instance for a group along with the case count from the case queryset, a la the depicted JSON data for Julie, Jenny and Juliet.

What I have now is the following:

```python
    def get_queryset(self):
        # cases is Queryset containing all of the cases in
        # all of the groups that a user has permission to access. 
        cases = case_query_filter(self.request.user)
        # cases is the related name from Case.group to Group
        case_filter = Q(cases__in=cases)

        return (
            Group.objects.filter(case_filter)
            .annotate(case_count=Count("cases", filter=case_filter))
            .filter(case_count__gt=0)
        )

```

This queryset correctly returns only the groups with cases that a user can access. That is, Julie and Jenny does not see an insects group, but Juliet does.

The issue that I face is that the case count that is returned is the count for Case.objects.all(group=group).count() as opposed to the count of cases in each group to which the user has access.

**Result from Group endpoint**

```json
[
    {
        "name": "birds",
        "case_count": 8, # this should be 2
    },
    {
        "name": "insects",
        "case_count": 20, # this should be 3
    }
]

```

Apologies if this was a bit long winded, but I felt that it would help with a quite verbose explaination.

Super hero status to anyone who can point my in the right direction.

Cheers,

C

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [March 14, 2021, 9:19pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/2 "2021-03-14T21:19:11Z")

</div>

Hi Conor, have you tried passing [`distinct=True`](https://docs.djangoproject.com/en/3.1/ref/models/querysets/#id8) in `Count`?

---

<div class="post-metadata">

**Author:** ![conor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/conor/32/3736_2.png) [@conor](https://forum.djangoproject.com/u/conor)\
**Post date:** [March 14, 2021, 9:41pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/3 "2021-03-14T21:41:05Z")

</div>

Hi Tim,

No, I hadn’t, as I didn’t think it would be of assistance in this instance. I’m getting distinct groups in my group queryset, but the count reflects the count of all cases related to the group, not the count of cases related to the group to which the user has access.

For good measure I added `distinct=True` but it didn’t change the result.

```python
        return (
            Group.objects.filter(case_filter)
            .annotate(case_count=Count("cases", filter=case_filter, distinct=True))
            .filter(case_count__gt=0)
        )

```

Is this along the lines of what you were thinking, or I have I misinterpreted what you were suggesting?

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [March 15, 2021, 1:43pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/4 "2021-03-15T13:43:07Z")

</div>

Yes it was. The results really look like duplicates. Strange. Can you print the QuerySet out? Sometimes I find looking at the raw SQL help reveal the problem.

```auto
print(queryset.query)

```

---

<div class="post-metadata">

**Author:** ![conor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/conor/32/3736_2.png) [@conor](https://forum.djangoproject.com/u/conor)\
**Post date:** [March 15, 2021, 9:24pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/5 "2021-03-15T21:24:00Z")

</div>

Hi Tim,

Thanks for bearing with me. The data:

```json
[
    {
        "name": "birds",
        "case_count": 8, # this should be 2
    },
    {
        "name": "insects",
        "case_count": 20, # this should be 3
    }
]

```

is sound to my eyes with respect to grouping of the `groups` if my understanding of the query I am trying to write adds up. That is, there are two groups, one named `birds` and the other `insects` and each group has a total 8 and 20 cases, respectively. I expect there two be two groups in the result, but the tally of the case\_count should be less as the user has only access to 2 bird cases and 3 insect cases.

The query looks like this.

```sql
SELECT "cases_group"."id",
                "cases_group"."name",
                "cases_group"."display_name",
                Count(DISTINCT "cases_case"."uuid") filter (WHERE "cases_case"."uuid" IN
                (
                       SELECT "cases_case"."uuid"
                       FROM "cases_case")) AS "case_count"
FROM "cases_group"
LEFT OUTER JOIN "cases_case"
ON (
                                "cases_group"."id" = "cases_case"."group_id")
WHERE "cases_case"."uuid" IN
                (
                       SELECT "cases_case"."uuid"
                       FROM "cases_case")
GROUP BY "cases_group"."id"
HAVING count(DISTINCT "cases_case"."uuid") filter (WHERE (
                                "cases_case"."uuid" IN
                                (
                                       SELECT "cases_case"."uuid"
                                       FROM "cases_case"))) > 0

```

To my eye which last studied SQL nigh on 17 years ago, it looks like the where statement of the left out join:

```sql
LEFT OUTER JOIN "cases_case"
ON (
                                "cases_group"."id" = "cases_case"."group_id")
WHERE "cases_case"."uuid" IN
                (
                       SELECT "cases_case"."uuid"
                       FROM "cases_case")

```

Is select all case UUIDs from the cases table.

In my initial post, I have a variable which is a queryset with the cases a user is allowed to view. In the example we’ve been discussing, the queryset has 5 cases.

```python
cases = case_query_filter(self.request.user) # I have 5 cases
case_filter = Q(cases__in=cases) # I'm filtering the Count in the query below

queryset = (
    Group.objects.filter(case_filter)
    .annotate(case_count=Count("cases", filter=case_filter, distinct=True))
    .filter(case_count__gt=0)
)

```

So it appears to my confused eye that the filter in the `Count()` method is not behaving as I thought it would behave, or, probably more likely now that I think about it, my user of the filter, `case_filter` is off.

Thanks for the assistance so far, Tim.

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [March 15, 2021, 10:10pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/6 "2021-03-15T22:10:55Z")

</div>

Yeah, something seems off about what’s being returned from `case_query_filter` looking at your SQL.

A quick thing to test would be to force the cases returned from `case_query_filter` into a list, then use that in `case_filter`. This will verify that if you do have the correct cases being returned in a list that the count filtering works properly. And if it does give you the numbers you want, we know that the problem is in the combination of the two.

```auto
cases = list(case_query_filter(self.request.user))
case_filter = Q(cases__in=cases)

```

The other approaches that I can think of trying is are `COUNT(CASE(WHEN()))` or using trying to finagle a `Subquery` into returning an annotation.

---

<div class="post-metadata">

**Author:** ![conor](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/conor/32/3736_2.png) [@conor](https://forum.djangoproject.com/u/conor)\
**Post date:** [March 16, 2021, 2:02pm UTC](https://forum.djangoproject.com/t/querysets-aggregation-and-filtering-their-results-using-other-querysets/7044/7 "2021-03-16T14:02:37Z")

</div>

Something is definitely amiss here - myself or the code - I can’t tell yet . I cast the queryset into a list, but the same result, unfortunately. I’m going to do some more digging and when I find the solution or make some progress, I’ll post it here.

Hope to be back with some good news soon.