Filter api output by value stored in Join Table

Question

I have many to many relationship between two models (First is origin country with list of countries and second is destination country with list of countries).

I have created join table and I have set additional variable in it:

class BorderStatus(models.Model):
    STATUS_CHOICES = [("OP", "OPEN"), ("SEMI", "CAUTION"), ("CLOSED", "CLOSED")]
    origin_country = models.ForeignKey(OriginCountry, on_delete=models.CASCADE, default="0")
    destination = models.ForeignKey(Country, on_delete=models.CASCADE, default="0")
    status = models.CharField(max_length=6, choices=STATUS_CHOICES, default="CLOSED")
    extra = 1
    class Meta:
        unique_together = [("destination", "origin_country")]
        verbose_name_plural = "Border Statuses"

    def __str__(self):
        return (
            f"{self.origin_country.origin_country.name} -> {self.destination.name}"
            f" ({self.status})"
        )

Now I set the view that it lists a country and all countries related with a status, like this:

[
    {
        "name": "Germany",
        "destinations": [
        "New Zealand",
        "Watahia",
        "France"
        ],
        "dest_country": [
            {
                "id": 1,
                "name": "New Zealand",
                "status": "SEMI"
            },
            {
                "id": 2,
                "name": "Watahia",
                "status": "CLOSED"
            },
            {
                "id": 3,
                "name": "France",
                "status": "OP"
            }
        ]
    }
]

Here is my serializer:

class BorderStatusSerializer(serializers.HyperlinkedModelSerializer):
    id = serializers.ReadOnlyField(source='destination.id')
    name = serializers.ReadOnlyField(source='destination.name')

    class Meta:
        model = BorderStatus
        fields = ('id', 'name', 'status')

class OriginCountrySerializer(serializers.ModelSerializer):
    origin_country = serializers.StringRelatedField(read_only=True)
    destinations = serializers.StringRelatedField(many=True, read_only=True)
    dest_country = BorderStatusSerializer(source='borderstatus_set', many=True)

    class Meta:
        model = OriginCountry
        fields = ('origin_country', 'destinations', 'dest_country')

Now I want to add a filter that only shows coutries with for example Status="CLOSED"

So the result of the filtering for a given country and related countries with correct status would be: /?name=Germany&borderstatus__status=CLOSED

[
    {
        "name": "Germany",
        "destinations": ["Watahia"],
        "dest_country": [
            {
                "id": 2,
                "name": "Watahia",
                "status": "CLOSED"
            },
        ]
    }
]

However instead, that query is returning all of the countries from the realted table, regardless of status like seen in the first response above.

Here is the api view code:

class OriginCountryViewSet(viewsets.ModelViewSet):
    queryset = OriginCountry.objects.all()
    serializer_class = OriginCountrySerializer
    #use django filter backend instead of search filter to get query bu filter fields instead of `search=`
    filter_backends = (DjangoFilterBackend,)
    filter_fields=('origin_country','borderstatus__status',)

user_8283848 · Answer

The DRF List view is executed in the following order (essential steps related to OP),

request hits the view
calling get_queryset() method (This internally calls queryset attribute)
filtering the results using a list of filter_backends classes
serializing the data
returning to user/client

So, in your case, the filter_fields is being used at the 3rd step and that will not use again anywhere.

Solution

Use SerializerMethodField(...) and manually filter the related queryset as

class OriginCountrySerializer(serializers.ModelSerializer):
    origin_country = serializers.StringRelatedField(read_only=True)
    destinations = serializers.StringRelatedField(many=True, read_only=True)
    dest_country = serializers.SerializerMethodField()

    def get_dest_country(self, instance):
        qs = instance.borderstatus_set.all()
        request = self.context["request"]
        value = request.query_params.get("my_country_param")
        if value:
            qs = instance.borderstatus_set.filter(status=value)
        return BorderStatusSerializer(qs, many=True).data

    class Meta:
        model = OriginCountry
        fields = ('origin_country', 'destinations', 'dest_country')

and thus, you need to call the URL as

/?name=Germany&my_country_param=CLOSED

user_3670802 · Answer

You can use filter() instead of obejcts.all()


class CountryViewSet(viewsets.ModelViewSet):
    queryset = Country.objects.filter(status='CLOSED')

you add other conditions also


class CountryViewSet(viewsets.ModelViewSet):
    queryset = Country.objects.filter(status='CLOSED',country='Germany')

user_953000 · Answer

I think you need to update the name of your filter to:

filter_fields=('origin_country','borderstatus_set__status',)

Filter api output by value stored in Join Table

3 Answers

Solution