CODEWITHSUNDEEP
pythondjangodjango-rest-framework
SJ19
SJ19

Reputation: 2123

Django REST API - Joining multiple tables in a query

My goal is to get all EmployeeQuestions, such that employee has an employer with the given id (request.user.pk). More formally, I want to get all EmployeeQuestions where question has a course where employer_id is the given id.

I hope this makes sense. Basically as an employer I want to retrieve all the question progress of all my employees.

I thought I made the right query but it just returns all EmployeeQuestions:

EmployeeQuestion.objects.filter(question__in=CourseQuestion.objects.filter(course__in=Course.objects.filter(employer_id=request.user.pk)))

Any Django query geniuses here who can help me? :)

View

class EmployeeQuestionByEmployerId(viewsets.ModelViewSet):
    queryset = EmployeeQuestion.objects.all()
    serializer_class = EmployeeQuestionSerializer

    def list(self, request):
        queryset = EmployeeQuestion.objects.all()
        if request.query_params:
            queryset = EmployeeQuestion.objects.filter(question__in=CourseQuestion.objects.filter(course__in=Course.objects.filter(employer_id=request.user.pk)))

        serializer = EmployeeQuestionSerializer(queryset, many=True)
        return Response(serializer.data)

Models

class CustomUser(AbstractBaseUser):
    username        = ...
    employer        = models.ForeignKey("self", blank=True, null=True, on_delete=models.CASCADE)
    
class Course(models.Model):
    employer_id     = models.ForeignKey(CustomUser, on_delete=models.CASCADE, null=True)
    title           = models.CharField(max_length=255, blank=False, null=False)

class Question(models.Model):
    course          = models.ForeignKey(Course, on_delete=models.CASCADE, null=False)
    question        = models.CharField(max_length=255, blank=False, null=False)
    question_type   = models.CharField(max_length=255, blank=False, null=False) # mult/ord/open
    
class EmployeeQuestion(models.Model):
    employee        = models.ForeignKey(CustomUser, on_delete=models.CASCADE, null=True)
    question        = models.ForeignKey(CourseQuestion, on_delete=models.CASCADE, null=False)
    passed          = models.BooleanField(default=False)

Upvotes: 1

Views: 2294

Answers (1)

Ayush Bansal
Ayush Bansal

Reputation: 485

You can filter the foreign key model's attributes with the help of double underscores like this :

EmployeeQuestion.objects.filter(question__course__employer_id=request.user.pk)

It will automatically perform an inner join internally at the database level.

Upvotes: 2

Related Questions