CODEWITHSUNDEEP
djangodjango-rest-framework
KeykoYume
KeykoYume

Reputation: 2645

Join more than 2 tables in Django

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. enter image description here

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.

Upvotes: 1

Views: 277

Answers (1)

Andrew_Lvov
Andrew_Lvov

Reputation: 4668

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

Upvotes: 1

Related Questions