chained select_related from many tables in django

Question

Here I have the following models:

class GenericUserData(AbstractUser):
    username = None
    id = models.UUIDField(primary_key=True, default=uuid4, editable=False)
    email = models.EmailField(max_length=100, unique=True)
    date_joined = models.DateTimeField(auto_now_add=True)
   ....

class NormalSellerDetails(models.Model):
    userid = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, primary_key=True,db_column='UserID', unique=True)  # from table user.
    mobileauthenticationenabled = models.IntegerField(db_column='MobileAuthenticationEnabled', default=0)  # Field name made lowercase.
   .....

class UserBan(models.Model):
    user_id =  models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, primary_key=True,db_column='UserID', unique=True)  # from table user.
    from_date = models.DateField(null=False)

and I want to perform a select like the following:

SELECT * FROM genericuserdata, normalsellerdetails, userban 
WHERE genericuserdata.id = normalsellerdetails.UserID AND genericuserdata.id = userban.UserID

I also have there seializers:

class GenericUserRetrievalSerializer(serializers.ModelSerializer):
    class Meta:
        model = GenericUserData    
        fields = [ "email", "first_name", 'last_name', 'is_seller', 'is_normal', 'is_emp', 'is_male', 'bdate']


class AccountNormalSerializer(serializers.ModelSerializer):
    userid = GenericUserRetrievalSerializer(many=False)
    class Meta:
        model = NormalSellerDetails
        fields = ['mobileauthenticationenabled',
                'emailauthenticationenabled',
                'mobilephone',
                'profilepiclink',
                'userid',
                'userban'
                ]

I could link between GenericUserData, and NormalSellerDetails by doing so:

class NormalAccountList(generics.ListAPIView):
    serializer_class = AccountNormalSerializer

    def get_queryset(self):
         queryset = NormalSellerDetails.objects.all().select_related('userid')
         return queryset

but I couldn't link between all three into one query, noting model NormalSellerDetails relates to GenericUserData which in turn has an inverse relation to UserBan

My question is: 1- how to do that link?

2- userban table has rows for users who only are banned, How do create an output field having true when a row exists, and False when row doesn't exist?

also I tried to link by doing so, but didn't work

class NormalAccountList(generics.ListAPIView):
    serializer_class = AccountNormalSerializer
    authentication_classes = [JWTAuthentication]
    permission_classes = [IsEmpReadAllUsersOrPost]

    def get_queryset(self):
        queryset= NormalSellerDetails.objects.select_related('userid').select_related('userid__userban_set').all()
        
        return queryset

class AccountNormalSerializer(serializers.ModelSerializer):
    userid = GenericUserRetrievalSerializer(many=False)
    userban = UserBanSerializer(many=False, read_only=True)
    class Meta:
        model = NormalSellerDetails
        fields = ['mobileauthenticationenabled',
                'emailauthenticationenabled',
                'mobilephone',
                'profilepiclink',
                'userid',
                'userban'
                ]

it was telling me " Invalid field name(s) given in select_related: 'userban_set'. Choices are: linkcounter, normalsellerdetails, permissions, userban" and when I remove the keyword set it doesn't actually return anything from userban table.

Answer 1

Your queryset should look something like this:

queryset = NormalSellerDetails.objects.select_related('userid', 'userid__userban').all()

Note here that as indicated by the error you get, userban is the field you'll look for and not userban_set.

Regarding your second question, in order to have a field indicating if user is banned or not, you can annotate this field on the queryset like:

from django.db.models import Case, When, BooleanField


queryset = NormalSellerDetails.objects.select_related(
    'userid', 'userid__userban').all().annotate(
     is_banned=Case(
         When(userid__userban__isnull=False, then=True),
         default=False,
         output_field=BooleanField()
     )
)

You can then add this field in your serializer fields and it should appear in your API response.

Note here that ideally you should name your attribute in NormalSellerDetails model as user instead of userid. Of course, on the SQL level these relationships are by the foreign key which is essentially an ID, however, referring it as user means you're going to the user table and then doing something with the attributes within that table. This would make your Django code more readable.