How to filter records in complex DB by the fields in related models in DRF?

Question

We want to let possibility to find in all tables the records by the fields in related tables. Actually we would like to search through all of them.

For example the code:

/models.py

class Tourney(models.Model):
    tourney = models.CharField()    
    tourney_1 = models.CharField() 
    tourney_2 = models.CharField()    

class Stage(models.Model):
    tourney = models.ForeignKey(Tourney, on_delete=models.CASCADE)
    stage = models.CharField()
    stage_1 = models.CharField()
    stage_2 = models.CharField()

class Group(models.Model):
    stage = models.ForeignKey(Stage, on_delete=models.CASCADE)
    group = models.CharField()
    group_1 = models.CharField()
    group_2 = models.CharField()

Group has relation on Stage which has relation on Tourney.

So now we want to set API for them. Imagine we have simple serializers for them which include all their fields and called TourneySerializer, StageSerializer and GroupSerializer.

Now let's try to filter the Group model.

/views.py

class GroupViewSet(viewsets.ModelViewSet):
    queryset = Group.objects.all()
    serializer_class = serializers.GroupSerializer

    def list(self, request, *args, **kwargs):
        queryset = self.get_queryset()

        if 'tourney' in request.GET:
            queryset = queryset.filter(stage__tourney__tourney=request.GET['tourney'])
        if 'tourney_1' in request.GET:
            queryset = queryset.filter(stage__tourney__tourney_1=request.GET['tourney_1'])
        if 'tourney_2' in request.GET:
            queryset = queryset.filter(stage__tourney__tourney_2=request.GET['tourney_2'])
        if 'stage' in request.GET:
            queryset = queryset.filter(stage__stage=request.GET['stage'])
        if 'stage_1' in request.GET:
            queryset = queryset.filter(stage__stage_1=request.GET['stage_1'])
        if 'stage_2' in request.GET:
            queryset = queryset.filter(stage__stage_2=request.GET['stage_2'])
        if 'group' in request.GET:
            queryset = queryset.filter(group=request.GET['group'])
        if 'group_1' in request.GET:
            queryset = queryset.filter(group_1=request.GET['group_1'])
        if 'group_2' in request.GET:
            queryset = queryset.filter(group_2=request.GET['group_2'])

        serializer = self.get_serializer_class()(
            queryset,
            many=True)

        return Response(serializer.data)

Here we have one ViewSet with a bunch of obvious code and there will be more if there are more tables and more fields in the tables. I have up to 20 tables and the last table in this related chain can be able to filter though about 40 fields. So it's possible to have about 400 filter rules on all models so it's about 800 lines of stupid code just for one last model. Not good at all.

So is there any right known way to do this? This problems looks common so maybe are there embedded solutions from Django or any libraries of it?

Answer 1

The fastest way it is change the parameters name you pass to that view GET['tourney_2'] should available as GET['stage__tourney__tourney_2']. Than the whole filtering will be:

queryset = self.get_queryset().filter(**request.GET)

Of course you should check parameters name to avoid SQL injection like request.GET['deleted'] = True

In real project you can not use **request.GET. You should cast it to normal Python dict filters = dict(request.GET) and check filters against sql injection attacks.

queryset = self.get_queryset().filter(**filters)

Please look how to build query dynamically.

How to dynamically provide lookup field name in Django query?