Join more than 2 tables in Django

Question

I am trying to create a new endpoint that would return a joined result of 3 tables (without having the model class)

The 3 models that I am trying to join are: User, Institution and Site in order to display the users with the name of the site they belong to.

I have defined my own serializer as follows:

  class DashboardSerializer(serializers.Serializer):
    id = serializers.CharField(read_only=True)
    email = serializers.CharField()
    first_name = serializers.CharField()
    last_name = serializers.CharField()
    full_address = serializers.CharField()
    country = serializers.CharField()
    status = serializers.CharField()
    role = serializers.CharField()
    date_joined = serializers.DateTimeField()
    institution_id = serializers.CharField()
    site_id = serializers.CharField()
    site_name = serializers.CharField()

    def create(self, validated_data):
        pass

    def update(self, instance, validated_data):
        pass

and the view set:

class DashboardViewSet(viewsets.ModelViewSet):

    queryset = User.objects.all().select_related('institution_id').select_related('site_id')
    serializer_class = DashboardSerializer

    @api_view(['GET'])
    def get(self, request):
        if request.method == 'GET':
            users = User.objects.all().select_related('institution_id').select_related('site_id')
            serializer = DashboardSerializer(users, many=True)
            return JsonResponse(serializer.data, safe=False)

The problem is that when hitting the endpoint I get:

Invalid field name(s) given in select_related: 'institution_id', 'site_id'. Choices are: institution

I am not sure if this is a problem with the query, serializer or even both. Any help would be greatly appreciated.

You can use annotation here. – Willem Van Onsem Sep 13 '18 at 10:00 — Willem Van Onsem, Sep 13 '18 at 10:00

Andrew_Lvov · Answer 1 · 2018-09-13T11:33:08.227

When you add select_related, it relies on field names, not DB column names. In your model you don't have a field institution_id but rather institution

Source: https://docs.djangoproject.com/en/2.1/ref/models/querysets/#select-related

Also, as far as I know, you cannot do multiple levels of select_related, but you can with prefetch_related.

Try:

User.objects.all().prefetch_related('institution_set, institution_set__site_set')

PLease see an answer here: https://stackoverflow.com/a/27117246/86698

Join more than 2 tables in Django

1 Answers1