# Django DRF: how to groupby on a foreign fields?

**URL:** <https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110>\
**Category:** Using the ORM\
**Created:** [July 30, 2022, 10:42am UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110 "2022-07-30T10:42:29Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![MrCordeiro](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mrcordeiro/32/785_2.png) [@MrCordeiro](https://forum.djangoproject.com/u/MrCordeiro)\
**Post date:** [July 30, 2022, 10:42am UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/1 "2022-07-30T10:42:29Z")

</div>

I have a model where users can upvote other users for specific topics. Something like:

```python
#models.py
Class Topic(models.Model):
    name = models.StringField()

    def __str__ (self):
        return str(self.name)

Class UserUpvotes(models.Model):
    """Holds total upvotes by user and topic"""
    user = models.ForeignKey(User)
    topic= models.ForeignKey(Topic)
    upvotes = models.PositiveIntegerField(default=0)

```

Using DRF, I have an API that returns the following: `topic_id`, `topic_name`, and `upvotes`, which is the total upvotes for a given topic.

> One of the project requirements is for the API to use these field names specifically: `topic_id`, `topic_name`, and `upvotes`

```python
#serializers.py
class TopicUpvotesSerializer(serializers.ModelSerializer):
    topic_name = serializers.StringRelatedField(source="topic")

    class Meta:
        model = UserUpvotes
        fields = ["topic_id", "topic_name", "upvotes"]

```

My trouble is aggregating these fields. I’m filtering the `UserUpvotes` by user or team and then aggregating by topic.

## Desired output

This is the result I want to get. When I _don’t_ perform any aggregations (and there are views where this will be the case), it works.

```json
[
    {
        "topic_name": 3,
        "topic_name": "Korean Studies",
        "upvotes": 14
    },
    {
        "topic_name": 12,
        "topic_name": "Inflation",
        "upvotes": 3
    },
]

```

At first, I tried creating a `TopicSerializer`, and then assigning it to the `topic` field in `TopicUpvotesSerializer`. But then, the resulting json would have a nested “topic” field and the aggragation would fail.

## Attempt 1

```python
#views.py

def get_queryset(self):
    return (
      UserUpvotes.objects.filter(user__team=team)
          .values("topic")
          .annotate(upvotes=models.Sum("upvotes"))
          .order_by("-upvotes")
      )

```

My problem is that the `topic_id` and `topic_name` fields are not showing. I get something like:

```json
[
    {
        "topic_name": "3",
        "upvotes": 14
    },
    {
        "topic_name": "12",
        "upvotes": 3
    },
]

```

## Attempt 2

Another queryset attempt:

```python
# views.py

def get_queryset(self):
    return (
      UserUpvotes.objects.filter(user__team=team)
          .values("topic __id", "topic__ name")
          .annotate(upvotes=models.Sum("upvotes"))
          .order_by("-upvotes")
      )

```

Which yields:

```json
[
    {
        "upvotes": 14
    },
    {
        "upvotes": 3
    },
]

```

The aggregation worked on the queryset level, but the serializer failed to find the correct fields.

## Attempt 3

This was the closest I got:

```python
# views.py

def get_queryset(self):
    return (
      UserUpvotes.objects.filter(user__team=team)
          .values("topic __id", "topic__ name")
          .annotate(upvotes=models.Sum("upvotes"))
          .values("topic_id", "topic", "upvotes")
          .order_by("-upvotes")[:n]
      )

```

```json
[
    {
        "topic_name": 3,
        "topic_name": "3",
        "upvotes": 14
    },
    {
        "topic_name": 12,
        "topic_name": "12",
        "upvotes": 3
    },
]

```

I have no idea why “topic\_name” is simply transforming the “topic\_id” into a string, instead of calling the string method.

---

<div class="post-metadata">

**Author:** ![MrCordeiro](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mrcordeiro/32/785_2.png) [@MrCordeiro](https://forum.djangoproject.com/u/MrCordeiro)\
**Post date:** [July 30, 2022, 10:49am UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/2 "2022-07-30T10:49:14Z")

</div>

Another thing I tried was to customize the “topic\_name” field:

```python
class UserUpvotes(serializers.ModelSerializer):
    topic_name = serializers.SerializerMethodField()

    def get_topic_name (self, obj):
        return obj.topic.name

    class Meta:
        model = UserUpvotes
        fields = ["topic_id", "topic_name", "upvotes"]

```

The rub is that `obj` is a dict, which I wasn’t expecting. Maybe due to the `values().annotate()` aggregation?

There will be times there won’t be any aggregations, and then I’ll get a `'UserUpvotes' object is not subscriptable` error.

This is when I REALLY miss type hints in Django. I could write a try-except, but it feels wrong. Plus I don’t know if there are any other cases I should be aware of.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 31, 2022, 12:12am UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/3 "2022-07-31T00:12:36Z")

</div>

> [@MrCordeiro](#):
>
> The rub is that `obj` is a dict, which I wasn’t expecting. Maybe due to the `values().annotate()` aggregation?

I can’t help you with any of the other questions you’ve posed - but this one is rather straight-forward.

See the docs for [`values`](https://docs.djangoproject.com/en/4.0/ref/models/querysets/#values) - when iterating over a queryset using the `values` function, you’re iterating over a dict, not a list of object instances. (The `annotate` function has nothing to do with this.)

---

<div class="post-metadata">

**Author:** ![MrCordeiro](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/mrcordeiro/32/785_2.png) [@MrCordeiro](https://forum.djangoproject.com/u/MrCordeiro)\
**Post date:** [August 3, 2022, 5:28pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/4 "2022-08-03T17:28:33Z")

</div>

That was not the point though. The point is that what you are essentially doing is a _group by_ operation. The fact this aggregation returns a list of dict and not a QuerySet is a curveball: after all, you can perform the inverse operation, by aggregating topics instead, and then get a QuerySet:

```python
(
 Topic.objects.filter(userupvote_set __user__ team=team)
  .annotate(upvotes=models.Sum("userupvote_set__upvotes")
) -> QuerySet

```

In fact, you could also run raw SQL, and get a `RawQuerySet` instead. But running the canonical way yields dicts.

In fact, I even found a library for previous Django versions that tries to hack the QuerySet back to a group by operation on the previous model: [GitHub - kako-nawao/django-group-by: Have your valuesquerysets return model instances instead of dicts](https://github.com/kako-nawao/django-group-by)

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 3, 2022, 5:36pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/5 "2022-08-03T17:36:01Z")

</div>

But that _is_ the point.

The difference in your example here is that you’re not using the `values` function in this query.

It’s the `values` function that causes the conversion of a “queryset of object instances” to a “queryset of a list of dict” as documented at [QuerySet API reference | Django documentation | Django](https://docs.djangoproject.com/en/4.0/ref/models/querysets/#values)

To change the semantics of the `values` function in _this_ case to make it return a “queryset of object instances” would make it inconsistent with the documentation and with every other instance where `values` is being used.

---

<div class="post-metadata">

**Author:** ![qvisty](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/qvisty/32/4818_2.png) [@qvisty](https://forum.djangoproject.com/u/qvisty)\
**Post date:** [August 3, 2022, 7:43pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/6 "2022-08-03T19:43:05Z")

</div>

> [@KenWhitesell](#):
>
> It’s the `values` function that causes the conversion of a “queryset of object instances” to a “queryset of a list of dict”

Just to be curious. Where would that be preferred?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 3, 2022, 8:00pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/7 "2022-08-03T20:00:49Z")

</div>

> [@qvisty](#):
>
> Where would that be preferred?

Which is the “that” you are referring to?

The second post in this thread contains:

> [@MrCordeiro](#):
>
> The rub is that `obj` is a dict, which I wasn’t expecting.

The author has a query at the top containing a `values` clause, and was not expecting this behavior in that query. So in this specific situation, the author would have preferred that `values` would return a “queryset of instances” instead of the “queryset of a list of dict”.

---

<div class="post-metadata">

**Author:** ![samul-1](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/samul-1/32/4767_2.png) [@samul-1](https://forum.djangoproject.com/u/samul-1)\
**Post date:** [August 3, 2022, 8:17pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/8 "2022-08-03T20:17:17Z")

</div>

> [@KenWhitesell](#):
>
> Which is the “that” you are referring to?

I believe the question is, in which cases would you prefer dealing with dicts rather than model instances?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [August 3, 2022, 8:28pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/9 "2022-08-03T20:28:34Z")

</div>

That’s actually the _purpose_ of the `values` clause. It saves the overhead of instantiating instances of objects when you’re only interested in the values of specific fields.

It’s also appropriate in the cases like that of the “group by” aggregation as described above, in that what the query is returning is not a set of object instances.

Technically speaking, a “group by” result set are not rows from the table being aggregated. It makes no sense to refer to model fields from a group by aggregation that aren’t the aggregated values or the groups over which the aggregation is being performed - the results would be indeterminate.

---

<div class="post-metadata">

**Author:** ![qvisty](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/qvisty/32/4818_2.png) [@qvisty](https://forum.djangoproject.com/u/qvisty)\
**Post date:** [August 3, 2022, 8:55pm UTC](https://forum.djangoproject.com/t/django-drf-how-to-groupby-on-a-foreign-fields/15110/10 "2022-08-03T20:55:59Z")

</div>

> [@KenWhitesell](#):
>
> It saves the overhead of instantiating instances of objects when you’re only interested in the values of specific fields.

Thank you. 🙂