Trying to do an INNER Join and display the result on the API

Question

This question has been asked before but I cannot use any of the answers to my case.

I'm trying to have the equivalent of this, to show the results on the API.

SELECT denom_name,retail_name,retail_adr 
FROM denomination d INNER JOIN Retailer r 
ON r.id = d.retailer.id

These are my models (models.py):

class Retailer(models.Model):
    retail_name = models.CharField(max_length=30)
    retail_addr = models.CharField(max_length=300,null=True)
    def __str__(self):
        return self.retail_name

class Denomination(models.Model):
    denom_name = models.CharField(max_length=1000)
    retailer = models.ForeignKey(Retailer, on_delete=models.CASCADE)

I've created a viewset on the views.py

class DenomRetailViewset(viewsets.ModelViewSet):

    queryset = Denomination.objects.select_related('Retailer')
    serializer_class =  DenomRetailSerializer

But here lies the issue, at least one of them.

I'm creating the serializer through the serializer.py

class DenomRetailSerializer(serializers.HyperlinkedModelSerializer):

    class Meta:
        model= retailer,denomination
        fields = ('denom_name','retail_name','retail_adr')

But as you can see, the serializer cannot accept two models. And beside, I have doubts about the viewset, queryset = Denomination.objects.select_related('Retailer').

Any tips are more than welcomed as I'm starting to lose my sanity.

Thanks.

If `Retailer` _must_ exist ( as implied by "`inner join`") you'll want to change your `queryset` like `queryset = Denomination.objects.select_related('Retailer').filter(retailer__isnull=False)` — Ross Rogers, Jul 15 '20 at 16:51

score 2 · Accepted Answer · answered Jul 15 '20 at 16:44

2

Use source--DRF doc argument

class DenomRetailSerializer(serializers.HyperlinkedModelSerializer):
    retail_name = serializers.CharField(source='retailer.retail_name')
    retail_adr = serializers.CharField(source='retailer.retail_adr')

    class Meta:
        model = Denomination
        fields = ('denom_name', 'retail_name', 'retail_adr')

Also, it should be .select_related('retailer') instead of .select_related('Retailer')

answered Jul 15 '20 at 16:44

JPG

82,442
19
127
206

1

Hi @arrakal-abu, great answer. Thank you. Points are yours. – Andy K Jul 16 '20 at 12:28

score 0 · Answer 2 · answered Jul 16 '20 at 04:42

You can use "depth" in this case:

class DenomRetailSerializer(serializers.HyperlinkedModelSerializer):

    class Meta:
        model= denomination
        fields = ('denom_name','retail')
        depth = 1

The response will return an inner join like that:

{
    "id": 1,
    "denom_name ": "...",
    "retail": {
        "id" : 1,
        "retail_name" : "...",
        "retail_addr" : "..."
    }
}

Trying to do an INNER Join and display the result on the API

2 Answers2