How to filter Foreign Key relations on Django serializers?

Question

How to generate left outer join query using reverse foreign key relation and map it to the nested serializer? I want to filter result on multiple foreign keys.

models.py

class Question(models.Model):
    question_name = models.CharField(max_length=1024) 

class Paper(models.Model):    
    paper_name = models.CharField(max_length=128)

class Answer(models.Model):
    score = models.IntegerField()
    question_id = models.ForeignKey(Question, related_name='answer_questions')
    paper_id = models.ForeignKey(Paper, related_name='answer_papers')
 class Meta:
    unique_together = ("question_id", "paper_id")

serializers.py

class PaperSerializer(serializers.ModelSerializer):        
    class Meta:
        fields = ('id', 'paper_name')
        model = Paper            

class AnswerSerializer(serializers.ModelSerializer):
    class Meta:
        fields = ('id', 'score', 'question_id', 'paper_id')
        model = Answer    

class QuestionSerializer(serializers.ModelSerializer):
    answer_questions = AnswerSerializer(many=True, allow_null=True)    
    class Meta:
        fields = ('id', 'question_name', 'answer_questions')
        model = Question

views.py

class QuestionList(generics.ListAPIView):      
    def get_queryset(self):
        paper_id = self.kwargs['pk']
        queryset = Question.objects.filter(answer_questions__paper_id=paper_id, answer_questions__question_id=F('id'))
        return queryset

    serializer_class = QuestionSerializer

My url is localhost/papers/1/questions/

Expected output: (1) List of all questions with answer object for corresponding question_id and paper_id only (single answer object) embedded in it. (2) List should include all questions irrespective of they are answered or not (in case of question not answered, question with empty answer object should be returned) i.e. left outer join

[
    {
        "id": 1,
        "question_id": 1,
        "answer_questions": [
            {
                "id": 24,
                "score": 5,
                "question_id": 1,
                "paper_id": 1
            },
            {
                "id": 27,
                "score": 8,
                "question_id": 1,
                "paper_id": 2
            },
            {
                "id": 28,
                "score": 6,
                "question_id": 1,
                "paper_id": 3
            }
        ]
    }
]    

Current Output: (1) I am getting multiple answer objects for particular question_id including all paper_ids. i.e. For question_id = 1 and paper_id = 2, My output shows questions with answer objects for question_id = 1 not filtering on paper_id. (2) Only answered questions (as query is inner join query)

[
    {
        "id": 1,
        "question_id": 1,
        "answer_questions": [
            {
                "id": 24,
                "score": 5,
                "question_id": 1,
                "paper_id": 1
            }
        ]
    }
]

If it's not a good way to implement, kindly suggest better way to optimize it.

Answer 1

I think you'll get desired output if you slightly change the get_quesyset() method.

class QuestionList(generics.ListAPIView):
    def get_queryset(self):
        return Question.objects.all()

    serializer_class = QuestionSerializer

When you access the QuestionList list api you will get the output as follows

[
    {
        "id": 4,
        "question_name": "qus-1",
        "answer_questions": [
            {
                "id": 5,
                "score": 50,
                "question_id": 4,
                "paper_id": 4
            },
            {
                "id": 6,
                "score": 10,
                "question_id": 4,
                "paper_id": 5
            }
        ]
    },
    {
        "id": 5,
        "question_name": "qus-2",
        "answer_questions": []
    },
    {
        "id": 6,
        "question_name": "que-3",
        "answer_questions": [
            {
                "id": 7,
                "score": 342,
                "question_id": 6,
                "paper_id": 4
            }
        ]
    }
]

Update-1

Change your serializer as below

class QuestionSerializer(serializers.ModelSerializer):
    answer_questions = serializers.SerializerMethodField()

    def get_answer_questions(self, question):
        paper_id = self.context['view'].kwargs.get('paper_id')
        return AnswerSerializer(question.answer_questions.filter(paper_id=paper_id), many=True).data

    class Meta:
        fields = ('id', 'question_name', 'answer_questions')
        model = Question