Returning queryset that uses a foreign key of a foreign key relationship

Question

It sounds simple enough: get all the Users for a specific Company. But it is complicated by a few things:

• The User model is extended
• The model that extends it contains a UUID that uniquely identifies the user throughout various system integrations
• This UUID is what is used in the company relationship.

So the relationship is user_to_company__user_uuid -> user_extended__user_id -> auth_user. That's what I'd like to return is the User model.

What I have:

# url
/api/user_to_company/?company_uuid=0450469d-cbb1-4374-a16f-dd72ce15cf67

# views.py
class UserToCompanyViewSet(mixins.ListModelMixin,
                           mixins.RetrieveModelMixin,
                           viewsets.GenericViewSet):
    filter_backends = [StrictDjangoFilterBackend]
    filterset_fields = [
        'id',
        'user_uuid',
        'company_uuid'
    ]
    permission_classes = [CompanyPermissions]

    def get_queryset(self):
        if self.request.GET['company_uuid']:
            queryset = User.objects.filter(
                user_to_company__company_uuid=self.request.GET['company_uuid'])
        return queryset

    def get_serializer_class(self):
        if self.request.GET['company_uuid']:
            serializer_class = UserSerializer
        return serializer_class

# serializers.py

class UserToCompanySerializer(serializers.ModelSerializer):
    class Meta:
        model = UserToCompany
        fields = '__all__'


class UserExtendedSerializer(serializers.ModelSerializer):
    class Meta:
        model = UserExtended
        fields = '__all__'

# models.py

class UserExtended(models.Model):

    user_id = models.OneToOneField(
        User, on_delete=models.CASCADE, db_column='user_id')
    uuid = models.UUIDField(primary_key=True, null=False)

    class Meta:
        db_table = 'user_extended'

class UserToCompany(models.Model):
    user_uuid = models.ForeignKey(
        UserExtended, on_delete=models.CASCADE, db_column='user_uuid', related_name='user_to_company')
    company_uuid = models.ForeignKey(
        Companies, on_delete=models.CASCADE, db_column='company_uuid', related_name='user_to_company')

    class Meta:
        db_table = 'user_to_company'
        unique_together = [['user_uuid', 'company_uuid']]

Understandably, in this setup User.object.filter(user_to_company__company_uuid=self.reqest.GET['company_uuid'] doesn't make sense and it returns django.core.exceptions.FieldError: Cannot resolve keyword 'user_to_company' into field--There isn't a relationship between User and UserToCompany directly, but there is between User -> UserExtended -> UserToCompany

I could like do this by using UserExtended.object.filter(), but that returns an object like :

[
  {
    "user_extended_stuff_1": "stuff",
    "user_extended_stuff_2": "more stuff",
    "auth_user": {
      "auth_user_stuff_1": "stuff",
      "auth_user_stuff_2": "more stuff"
    }
  }
]

But I need an object like:

[
  {
    "auth_user_stuff_1": "stuff",
    "auth_user_stuff_2": "more stuff",
    "user_extended": {
      "user_extended_stuff_1": "stuff",
      "user_extended_stuff_2": "more stuff"
    }
  }
]

Is there a way to implement "foreign key of a foreign key" lookup?

I think a work around would get the list of users and then do something like User.objects.filter(user_ext__user_uuid__in=[querset])

Answer 1

Honestly, your endpoint and view are a little bit strange. Probably because you are thinking of using a intermediate model as ViewSet.

Instead what makes more sense is to have a CompaniesViewSet with an extra action where you can list all users for a given company. Also, you can access User and UserExtended in both ways using relations:

views.py:

class CompaniesViewSet(mixins.ListModelMixin,
                        mixins.RetrieveModelMixin,
                        viewsets.GenericViewSet):
    
    queryset = Companies.objects.all()
    serializer_class = CompaniesSerializer

    @action(detail=False, methods=['get'])
    def users(self, request):
        pk = request.GET.get('company_uuid', None)
        if pk:
            try:
                instance  = self.get_queryset().get(pk=pk)
                # I Would also change this related name
                qs = instance.user_to_company.all()

                user_list = []
                for obj in qs:
                    # Odd way to access because of your models fields.
                    serializer = UserSerializer(obj.user_uuid.user_id)
                    user_list.append(serializer.data)

                return Response(user_list)
            except ValidationError:
                return Response(
                    {'msg': 'Object with does not exist'}, 
                    status=status.HTTP_404_NOT_FOUND
                )
        else:
            return Response(
                {'msg': 'missing query string param'}, 
                status=status.HTTP_400_BAD_REQUEST
            )

serializers.py

from django.contrib.auth import get_user_model
from django.forms import model_to_dict

class UserSerializer(serializers.ModelSerializer):
    user_extended = serializers.SerializerMethodField()
    
    class Meta:
        model = get_user_model()
        fields = ['username', 'email', 'user_extended']

    def get_user_extended(self, instance):
        # Another odd way to access because of model name
        return model_to_dict(instance.userextended)

class CompaniesSerializer(serializers.ModelSerializer):
    class Meta:
        model = Companies
        fields = '__all__'

So, if you register the following:

router = routers.DefaultRouter()
router.register(r'companies', views.CompaniesViewSet)
urlpatterns = [
    path('api/', include(router.urls))
]

endpoint:

/api/companies/users/?company_uuid=0450469d-cbb1-4374-a16f-dd72ce15cf67