[FIXED] Filter by fields from foreignKey relationships

Issue

I got a bunch of models and some of them are connected (by foreign-key relationships) and I wrote a serializer which allows me to print out all of the connected fields that I want, and leave out what I do not want to see. Great. Now I also have a basic filter, which uses the model (PmP) which contains all the foreignkeys, but now I want to add another filter for a field (field name e from PmPr Model) from a different Model, one that is read in via foreignkey connection (li in Model PmP connects to model PmL containing field pro which connects to model PmPr where the field e is). But I dont know how to do that and as far as I can see, I cant set two filter_classes inside my view (PmPLListView)?! And I dont know how to access the field via the foreignkey relation. So how do I go about this? If I can access the e field from PmPr Model via my existing filter - than that is also fine with me, I dont necessary want two filter classes (if even possible). It was just me first thought. (btw. sorry about the strange names, but unfortunately I'm not allowed to write the real names)

these are my models (at least the relevant ones):

class PmP(models.Model):
    created_at = models.DateTimeField()
    pr = models.DecimalField(max_digits=6, decimal_places=2)
    li = models.ForeignKey(PmL, models.DO_NOTHING)
    se = models.ForeignKey('PmSe', models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'pm_p'



class PmL(models.Model):
    u = models.TextField()
    pro = models.ForeignKey('PmPr', models.DO_NOTHING)
    sh = models.ForeignKey('PmS', models.DO_NOTHING)
    active = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'pm_l'



class PmSe(models.Model):
    name = models.TextField()
    s_i_id = models.TextField(blank=True, null=True)
    sh = models.ForeignKey('PmS',
                             models.DO_NOTHING,
                             blank=True,
                             null=True)

    class Meta:
        managed = False
        db_table = 'pm_se'


class PmPr(models.Model):
    name = models.TextField()
    e = models.CharField(max_length=13)
    created_at = models.DateTimeField()
    cus = models.ForeignKey(PmC, models.DO_NOTHING)
    u_v_p = models.DecimalField(max_digits=10,
                              decimal_places=2,
                              blank=True,
                              null=True)
    cf = models.IntegerField(blank=True, null=True)
    s_k_u = models.IntegerField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'pm_pr'

this is what my serializer looks like:

class PmPLSerializer(serializers.ModelSerializer):
    # id = serializers.SerializerMethodField('get_l_id')
    u = serializers.SerializerMethodField('get_l_u')
    sh = serializers.SerializerMethodField('get_sh_name')
    name = serializers.SerializerMethodField('get_pro_name')
    e = serializers.SerializerMethodField('get_pro_e')
    u_v_p = serializers.SerializerMethodField('get_pro_u_v_p')
    s_k_u = serializers.SerializerMethodField('get_pro_s_k_u')
    se = serializers.SerializerMethodField('get_se_name')
    pr = serializers.SerializerMethodField('get_pr')
    created_at = serializers.SerializerMethodField('get_created_at')

    class Meta:
        model = PmP
        # fields = '__all__'
        fields = ('u', 'sh', 'name', 'e', 's_k_u', 'u_v_p', 'pr',
                  'created_at', 'se')
        depth = 2

    def get_l_id(self, obj):
        return obj.li.id

    def get_l_u(self, obj):
        return obj.li.u

    def get_sh_name(self, obj):
        return obj.li.sh.name

    def get_pro_name(self, obj):
        return obj.li.pro.name

    def get_pro_e(self, obj):
        return obj.li.pro.e

    def get_pro_u_v_p(self, obj):
        return obj.li.pro.u_v_p

    def get_pro_s_k_u(self, obj):
        return obj.li.pro.s_k_u

    def get_se_name(self, obj):
        return obj.se.name

    def get_pr(self, obj):
        return obj.pr

    def get_created_at(self, obj):
        return obj.created_at

this is my filter class:

class PmPFilter(rfilters.FilterSet):
    class Meta:
        model = PmP
        fields = [
            "created_at",
            "pr",
        ]

    for field in ["pr"]:
        exec(f'min_{field} = rfilters.NumberFilter(field, lookup_expr="gte")')
        exec(f'max_{field} = rfilters.NumberFilter(field, lookup_expr="lte")')


    # filter by date as "is_less_than_or_equal_to"
    written_to = rfilters.CharFilter(method="created_at_to", label="created_at to")

    # filter by date as "is_greater_than_or_equal_to"
    written_from = rfilters.CharFilter(method="created_at_from", label="created_at from")

    # filter by exact date
    written = rfilters.CharFilter(method="created_at_exact", label="created_at exact")

    def created_at_exact(self, queryset, name, value):
        year, month, day, hour, minute, second = self.parse_date(value)
        cdate = datetime(year, month, day, hour, minute, second)
        return queryset.filter(created_at=cdate)

    def created_at_to(self, queryset, name, value):
        year, month, day, hour, minute, second = self.parse_date(value)
        cdate = datetime(year, month, day, hour, minute, second)
        return queryset.filter(created_at__lte=cdate)

    def created_at_from(self, queryset, name, value):
        year, month, day, hour, minute, second = self.parse_date(value)
        cdate = datetime(year, month, day, hour, minute, second)
        return queryset.filter(created_at__gte=cdate)

    def parse_date(self, value):
        return (
            parser.parse(value).year,
            parser.parse(value).month,
            parser.parse(value).day,
            parser.parse(value).hour,
            parser.parse(value).minute,
            parser.parse(value).second,
        )

and finally, this is my view:

class PmPLListView(generics.ListAPIView):
    queryset = PmP.objects.all()
    serializer_class = PmPLSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter)
    ordering_fields = ["created_at", "pr"]
    filter_class = PmPFilter
    fields = ("created_at", "pr")
    filter_fields = fields
    search_fields = fields

    def get_queryset(self):
        """
        This view should return a list of all data
        """
        return PmP.objects.filter()

Solution

oh I got it! I can access the foreign relationship with two underscores. So I modified my Filter class to this:

class PmPFilter(rfilters.FilterSet):
    class Meta:
        model = PmPrice
        fields = [
            "created_at",
            "pr",
            "li__pro__e",
        ]
 ...

and inside my PmPLListView view I also added the double underscores to access the field:

class PmPLListView(generics.ListAPIView):
    queryset = PmP.objects.all()
    serializer_class = PmPLSerializer
    filter_backends = (DjangoFilterBackend, SearchFilter, OrderingFilter)
    ordering_fields = ["created_at", "pr"]
    filter_class = PmPFilter
    fields = ("created_at", "pr", "li__pro__e")
    filter_fields = fields
    search_fields = fields

now I can filter by field e

Answered By - b101