Django group by foreign key aggregation

I have three models like this:

class Coin(models.Model):
    symbol = models.Charfield()
    
class User(models.Model):
    phone_number = models.Charfield()
    
class Portfo(models.Model):

    class Meta:
        unique_together = (
            "user",
            "coin",
        )

    user = models.ForeignKey(
        to=User,
        on_delete=models.CASCADE,
        null=False,
        related_name="portfo",
    )
    coin = models.ForeignKey(
        to=Coin,
        on_delete=models.CASCADE,
        null=False,
        related_name="owners",
    )
    available = models.DecimalField(
        max_digits=40,
        decimal_places=20,
        default=Decimal(0),
    )
    blocked = models.DecimalField(
        max_digits=40,
        decimal_places=20,
        default=Decimal(0)
    )

I'm going to calculate each user's portfo value so I wanted to aggregate portfo objects grouped by users like this:

 [
    {
      "user_id":1,
      "portfo":{
           "coin_1_symbol":Decimal("1"),
           "coin_2_symbol":Decimal("2"),...
          }
    },
    {
     "user_id":2,...
    },...
]

or this:

[
 {
    "user_id":1,
    "portfo":[
           {"coin_symbol":"some_symbol","total":Decimal("1")},
           {"coin_symbol":"some_symbol2","total":Decimal("2")},...
    ]
  },...
]

I tried aggregation with values but it returns this result:

>> Portfo.objects.exclude(available=0,blocked=0).annotate(total=Sum(F("available")+F("blocked"))).values("user_id","total","coin__symbol")

[{"user_id":1,"coin__symbol":"some_symbol","total":Decimal("1")},{"user_id":1,"coin__symbol":"some_symbol2", "total":Decimal("2")},...]

Is there any way to do this with django orm?

You won't be able to that just using annotate. Preferred way to do such things is using Nested Serializers.

For now what you can do is manually convert output from your query into desired result by following code.

for d in data: # data is your mentioned output from query.
    user_id = d["user_id"]
    if not converted_data:
        converted_data.append({"user_id":user_id,"portfo":[d]})
        continue
    for c in converted_data:
        if c["user_id"] == user_id:
            c["portfo"].append(d)
            break
    else:
        converted_data.append({"user_id":user_id,"portfo":[d]})
Back to Top