Displaying table x's fields in GET request of table y that has foreign key of table x (Django)

Question

I could use some help please, been stuck on this for awhile.

I have two tables, Share and Fund.

Share model

class Share(models.Model):
ShareName = models.CharField(max_length=100, unique=True, default="N/A")
ISINCode = models.CharField(max_length=100, primary_key=True, default="N/A")

Fund model

class Fund(models.Model):
    FundID = models.AutoField(primary_key=True)
    FundName = models.CharField(max_length=100, default="N/A", unique=True)
    FundIdentifier = models.CharField(max_length=100, default="N/A")
    PercentOfFundNetAssets = models.DecimalField(decimal_places=2, max_digits=5, default=0.00)
    ISINCode = models.ForeignKey(Share, to_field='ISINCode', related_name="toshares",
                                 on_delete=models.CASCADE)

    class Meta: 
    unique_together = ('FundIdentifier', 'ISINCode')

Individually they has been no issue for GET/POST/PUT etc. Its important now though that on GET requests to fetch the fund table while also retrieving the relevant share fields that fall under each fund row. See below:

What fund JSON looks like now upon GET request

[
    {
        "FundName": "Some fund",
        "FundIdentifier": "FND001",
        "PercentOfFundNetAssets": "3.82",
        "ISINCode": "SHR001"
    },
]

What I want fund JSON to look like upon GET request

[
  {
    "FundName": "Some fund",
    "FundIdentifier": "FND001",
    "PercentOfFundNetAssets": 3.82,
    "ISINCode": "SHR001",
    "share": [
      {
        "ISINCode": "SHR001",
        "ShareName": "Some share"
      }
    ]
  }
]

I'd also settle for something like this though.

[
  {
    "FundName": "Some fund",
    "FundIdentifier": "FND001",
    "PercentOfFundNetAssets": 3.82,
    "ISINCode": "SHR001",
    "ISINCode": "SHR001",
    "ShareName": "Some share"
  }
]

Either way, each fund json object will only have one share object within with its relevant fields per row like below. Seems simple enough...but I keep encountering problems, so I'd appreciate someone to point out where I am going wrong.

ShareSerializer

class ShareSerializer(serializers.ModelSerializer):
class Meta:
    model = Share
    fields = ('ShareName', 'ISINCode')

def get_serializer(self, *args, **kwargs):
    if "data" in kwargs:
        data = kwargs["data"]

        # check if many is required
        if isinstance(data, list):
            kwargs["many"] = True

    return super(ShareViewSet, self).get_serializer(*args, **kwargs)

FundSerializer

class FundSerializer(serializers.ModelSerializer):
    toshares = ShareSerializer(many=True, read_only=True)

    class Meta:
        model = Fund
        fields = ('FundName', 'FundIdentifier', 'PercentOfFundNetAssets', 'ISINCode', 'toshares')

ShareViewSet

class ShareViewSet(ModelViewSet):
    authentication_classes = []
    permission_classes = []
    queryset = Share.objects.all().order_by('ISINCode')
    serializer_class = ShareSerializer
    paginator = None

    def get_serializer(self, *args, **kwargs):
        if "data" in kwargs:
            data = kwargs["data"]

            # check if many is required
            if isinstance(data, list):
                kwargs["many"] = True

        return super(ShareViewSet, self).get_serializer(*args, **kwargs)

FundViewSet

class FundViewSet(ModelViewSet):
    authentication_classes = []
    permission_classes = []

    queryset = Fund.objects.all().order_by('FundIdentifier')
    serializer_class = FundSerializer
    paginator = None

        def get_serializer(self, *args, **kwargs):
        if "data" in kwargs:
            data = kwargs["data"]

            # check if many is required
            if isinstance(data, list):
                kwargs["many"] = True

        return super(FundViewSet, self).get_serializer(*args, **kwargs)

Here's a PostgreSQL query that achieves what I want, but I haven't been able to emulate in Django:

SELECT * FROM fund f 
INNER JOIN share s ON s."ISINCode" = f."ISINCode_id" 
ORDER BY "FundID" ASC

What I've tried:

• I've tried using select_related in the queryset in fund viewset (see below):

  queryset = Fund.objects.select_related('ISINCode').all().order_by('FundIdentifier') for Fund in queryset: print(Fund.ISINCode)

I can access the share table fields that way (well, at least print them out to console) but I am unsure how to use this to add them to the queryset so I can get those columns/data in a GET request.

• related_name/set_share (tried both options but the related_name or set_share options in a queryset would never be recognised for some reason...)
• raw sql (manager.raw and cursor, first one doesn't seem to work for joining tables but works otherwise, cursor works but I don't know how to turn cursor.fetchall() into a format like a queryset). Also not the ideal solution anyway going by comments/documentation.
• Tried adding a filter to the queryset and referencing share__ShareName or some field
• Learned about python shell where some examples of this were done (but I use Postman, a frontend or PGAdmin to check my work instead) so would rather not use that if that can provide a solution somehow
• I've also seen references to a template file and using html to accomplish what I want, but I'd prefer not having to look into doing something like that.

Still very much a beginner at Django so I am sure one of the things I tried above was simply my doing it wrong and a lack of some of the fundamentals of Django. Appreciate any help here...the simpler the solution the better.

Thanks!

Answer 1

The problem I was having the whole time was not including a depth to the meta part of the fund serializer.

Changing

class Meta:
    model = Fund
    fields = ('FundName', 'FundIdentifier', 'PercentOfFundNetAssets', 'ISINCode', 'toshares')

to

class Meta:
    model = Fund
    fields = ('FundName', 'FundIdentifier', 'PercentOfFundNetAssets', 'ISINCode', 'toshares')
    depth = 1

fixed my problem. The default queryset I was using works fine once that change was made.

Get request now sends through something like this.

[
  {
    "FundName": "Some fund",
    "FundIdentifier": "FND001",
    "PercentOfFundNetAssets": 3.82,
    "ISINCode": {
        "ISINCode": "SHR001",
        "ShareName": "Some share"
      }   
  }
]