[FIXED] Django Rest Framework, group_by queries using .values(), and embedding a related record

Issue

I can't figure out how to embed a related record into a Django Rest Framework response when using .values() on a Django query set to return grouped by aggregated values. Sample Models, Serializers, and Viewset included below along with current response and a sample desired response.

Each Trade has one security. Multiple trades can exist for a security. I have a need to display Trades individually and also grouped by security. When grouping by security, I want to return the embedded security in the response along with the aggregate values for the associated trades, but so far I can only get the security id returned. Any attempt to get the security itself in the response results in an exception 'int' object has no attribute 'pk'

Trade model

class Trade(models.Model):
    security = models.ForeignKey('Security')
    ticker = models.CharField(
        max_length=128,
        null=True,
        blank=True,
        db_index=True,
    )
    shares = models.FloatField(db_index=True)
    value_usd = models.FloatField(db_index=True)
    days_to_trade = models.FloatField(db_index=True)
    trade_date = models.DateField(
        db_index=True,
        null=True,
        blank=True,
    )

Security model

class Security(models.Model):
    name = models.CharField(
        db_index=True,
        blank=True,
        null=True,
    )
    ipo_date = models.DateField(
        null=True,
        blank=True
    )

Trade View

class TradeViewSet(viewsets.ModelViewSet):
    def get_queryset(self):
        // param and filter setup/defaults snipped for clarity
        ...
        qs = Trade.objects.filter(**qs_filter)
        // if group_by param is passed in, then group the trades by ticker/security and return aggregate sums for value, shares, and days to trade
        if group_by:
            qs = qs.values('ticker', 'security_id').annotate(value_usd=Sum('value_usd'), shares=Sum('shares'), days_to_trade=Sum('days_to_trade'), num_trades=Count('security')).order_by('ticker')
            self.serializer_class = TradeByTickerSerializer
        return qs

    serializer_class = TradeSerializer

Trade serializer

class TradeSerializer(serializers.ModelSerializer):
    security = SecuritySerializer(many=False, read_only=True)
    class Meta:
        model = Trade
        fields = [
            'id',
            'security',
            'trade_date',
            'ticker',
            'shares',
            'value_usd',
            'days_to_trade',
        ]
        depth = 1

TradeByTicker serializer

class TradeByTickerSerializer(serializers.ModelSerializer):
    num_trades = serializers.IntegerField(read_only=True)
    // security = SecuritySerializer(many=False, read_only=True) // this gives an exception 
    class Meta:
        model = Trade
        fields = [
            // 'security', // I tried this but get an exception
            'security_id',
            'ticker',
            'shares',
            'value_usd',
            'days_to_trade',
            'num_trades',
        ]

Current output

{
    "results": [
        {
            "securityId": 123,
            "ticker": "ABC-US",
            "shares": 790611.154356048,
            "valueUsd": 15598758.0754448,
            "daysToTrade": 7.2460672754406,
            "numTrades": 1
        },
        {
            "securityId": 456,
            "ticker": "DEF-US",
            "shares": 1116548.93406872,
            "valueUsd": 29041437.7751273,
            "daysToTrade": 6.6811609336384,
            "numTrades": 3
        },
        {
            "securityId": 789,
            "ticker": "HIJ-US",
            "shares": 979099.946772097,
            "valueUsd": 16360760.1105617,
            "daysToTrade": 6.50616625094424,
            "numTrades": 2
        },
        ...
]

Desired output

{
    "results": [
        {
            "security": {
                "id": 123,
                "name": "Another Boring Company",
                "ipoDate": "1988-12-23"
            },
            "ticker": "ABC",
            "shares": 790611.154356048,
            "valueUsd": 15598758.0754448,
            "daysToTrade": 7.2460672754406,
            "numTrades": 1
        },
        {
            "security": {
                "id": 456,
                "name": "Def Jam Records",
                "ipoDate": "2001-04-24"
            },
            "ticker": "DEF",
            "shares": 1116548.93406872,
            "valueUsd": 29041437.7751273,
            "daysToTrade": 6.6811609336384,
            "numTrades": 3
        },
        {
            "security": {
                "id": 789,
                "name": "Hijinx Corp",
                "ipoDate": "1999-12-31"
            },
            "ticker": "HIJ",
            "shares": 979099.946772097,
            "valueUsd": 16360760.1105617,
            "daysToTrade": 6.50616625094424,
            "numTrades": 2
        },
        ...
]

Solution

You could use a SerializerMethodField to handle getting the related security details like this:

class TradeByTickerSerializer(serializers.ModelSerializer):
    num_trades = serializers.IntegerField(read_only=True)
    security = serializer.SerializerMethodField()

    class Meta:
        model = Trade
        fields = [
            'security',
            'security_id',
            'ticker',
            'shares',
            'value_usd',
            'days_to_trade',
            'num_trades',
        ]

    def get_security(self, obj)
        return SecuritySerializer(Security.objects.get(pk=obj['security_id'])).data

Or since the queryset does not containt Trade objects anymore, I would suggest to just use Serializer (non-ModelSerializer):

class TradeByTickerSerializer(serializers.Serializer):
    security = serializers.SerializerMethodField()
    ticker = serializers.FloatField(read_only=True)
    shares = serializers.FloatField(read_only=True)
    value_usd = serializers.FloatField(read_only=True)
    days_to_trade = serializers.FloatField(read_only=True)
    num_trades = serializers.IntegerField(read_only=True)

    def get_security(self, obj)
        return SecuritySerializer(Security.objects.get(pk=obj['security_id'])).data

Note though that this will hit the database per result of the group by queryset, just to fetch the related Security object.

Answered By - Brian Destura