DRF - filter across 2 models

Question

I'm working with a legacy database where I have a serializer setup on Table A like so -

class TblapplicationsSerializer(serializers.ModelSerializer):

    class Meta:
        model = Tblapplications
        fields = ('applicationid', 'applicationname', 'description', 'drtierid', 'saglink', 'supportinstructions',
                  'defaultincidentpriorityid', 'applicationorigintypeid', 'installationtypeid', 'comments',
                  'lastmodifieddate', 'lastmodifiedby', 'assetstatusid', 'recordownerid', 'adl_app')
        depth = 2

I'm using a standard filter -

class TblapplicationsFilter(django_filters.FilterSet):
    name = django_filters.CharFilter(name="applicationname", lookup_type="exact")
    env = django_filters.CharFilter(name="adl_app__environmentid__domain")

    class Meta:
        model = Tblapplications
        fields = ['applicationname', 'name', 'env']

Here's where it goes sideways. What I want to be able to do is filter on my URL like /api/applications/?name=xxx&env=DEV. It would then return the application and any databases that are linked with the environment of DEV. The name was understandably easy, but the only was I figured out the environment was to make the api point for applications touch the middle table that links the two but it returns multiple values because it's grabbing each time application is referenced with a separate database.

I've updated the Serializer and Filter based on comments given and the serializer, without the &env=DEV returns all the appropriate data (domain is nested in a reverse relationship). I then want my filter to filter the results based on that. Which means that it needs to somehow know to limit the results on the reverse relationship to only what's provided from the nested value.

If you see my models -

class Tblapplicationdatabaselinks(models.Model):
    id = models.AutoField(db_column='ID', primary_key=True)
    applicationid = models.ForeignKey('Tblapplications', db_column='applicationId', to_field='applicationid',
                                      related_name='adl_app')
    dbid = models.ForeignKey('Tbldatabases', db_column='dbId', to_field='id', related_name='adl_db')
    environmentid = models.ForeignKey('Tbldomaincodes', db_column='environmentId', to_field='id',
                                      related_name='adl_envlink')
    comments = models.TextField(blank=True)
    lastmodifieddate = models.DateTimeField(db_column='lastModifiedDate', blank=True, null=True)
    lastmodifiedby = models.CharField(db_column='lastModifiedBy', max_length=255, blank=True)
#    upsize_ts = models.TextField(blank=True) # This field type is a guess.

    class Meta:
        managed = False
        db_table = 'tblApplicationDatabaseLinks'

class Tblapplications(models.Model):
    applicationid = models.AutoField(db_column='applicationId', primary_key=True)
    applicationname = models.CharField(db_column='applicationName', max_length=255)
    description = models.TextField(blank=True)
    drtierid = models.ForeignKey(Tbldomaincodes, db_column='drTierID', blank=True, null=True, to_field='id',
                                 related_name='app_drtier')
    saglink = models.TextField(db_column='sagLink', blank=True)
    supportinstructions = models.TextField(db_column='supportInstructions', blank=True)
    defaultincidentpriorityid = models.IntegerField(db_column='defaultIncidentPriorityId', blank=True, null=True)
    applicationorigintypeid = models.IntegerField(db_column='applicationOriginTypeId')
    installationtypeid = models.ForeignKey(Tbldomaincodes, db_column='installationTypeId', to_field='id',
                                           related_name='app_insttype')
    comments = models.TextField(blank=True)
    assetstatusid = models.ForeignKey(Tbldomaincodes, db_column='assetStatusId', to_field='id',
                                      related_name='app_status')
    recordownerid = models.ForeignKey(Tblusergroups, db_column='recordOwnerId', blank=True, null=True,
                                      to_field='groupid', related_name='app_owner')
    lastmodifieddate = models.DateTimeField(db_column='lastModifiedDate', blank=True, null=True)
    lastmodifiedby = models.CharField(db_column='lastModifiedBy', max_length=255, blank=True)
#    upsize_ts = models.TextField(blank=True) # This field type is a guess.

    class Meta:
        managed = False
        db_table = 'tblApplications'

class Tbldatabases(models.Model):
    dbid = models.AutoField(db_column='dbId', primary_key=True)
    dbname = models.CharField(db_column='dbName', max_length=255)
    serverid = models.ForeignKey('Tblservers', db_column='serverId', to_field='serverid', related_name='db_serv')
    servicename = models.CharField(db_column='serviceName', max_length=255, blank=True)
    dbtypeid = models.IntegerField(db_column='dbTypeId', blank=True, null=True)
    inceptiondate = models.DateTimeField(db_column='inceptionDate', blank=True, null=True)
    comments = models.TextField(blank=True)
    assetstatusid = models.IntegerField(db_column='assetStatusId')
    recordownerid = models.IntegerField(db_column='recordOwnerId', blank=True, null=True)
    lastmodifieddate = models.DateTimeField(db_column='lastModifiedDate', blank=True, null=True)
    lastmodifiedby = models.CharField(db_column='lastModifiedBy', max_length=255, blank=True)
#    upsize_ts = models.TextField(blank=True) # This field type is a guess.

    class Meta:
        managed = False
        db_table = 'tblDatabases'

class Tbldomaincodes(models.Model):
    id = models.IntegerField(db_column='ID', primary_key=True)
    domain = models.CharField(primary_key=True, max_length=255)
    displayname = models.CharField(db_column='displayName', primary_key=True, max_length=255)
    displayorder = models.IntegerField(db_column='displayOrder', blank=True, null=True)
    comments = models.TextField(blank=True)
    lastmodifieddate = models.DateTimeField(db_column='lastModifiedDate', blank=True, null=True)
    lastmodifiedby = models.CharField(db_column='lastModifiedBy', max_length=255, blank=True)
#    upsize_ts = models.TextField(blank=True) # This field type is a guess.

    class Meta:
        managed = False
        db_table = 'tblDomainCodes'

Answer 1

Extend your filter set and reference the field in the other model:

class TblapplicationsFilter(django_filters.FilterSet):
    name = django_filters.CharFilter(name="applicationname", lookup_type="exact")
    env = django_filters.CharFilter(name="environmentid__name")
    #                                    ^^^^^^^^^^^^^^^^^^^

    class Meta:
        model = Tblapplications
        fields = ['applicationname', 'name', 'env']

Also, you may wish to name your ForeignKey fields without the id suffix, which is the Django convention. In Django, when you access Tblapplications.environmentid, it is normally a model instance, not the actual id integer itself.