How to join 3 or more than 3 models in one single query ORM?

Question

I am having 4 models linked with a foreign key,

class CustomUser(AbstractUser):
    username = None
    email = models.EmailField(('email address'), unique=True)
    phone_no = models.CharField(max_length=255, unique=True)

    USERNAME_FIELD = 'email'
    REQUIRED_FIELDS = []

    objects = CustomUserManager()

    def __str__(self):
        return self.email

class personal_profile(models.Model):
    custom_user = models.ForeignKey(CustomUser, on_delete=models.CASCADE)
    picture = models.ImageField(default='profile_image/pro.png', upload_to='profile_image', blank=True)
    role = models.CharField(max_length=255, blank=True, null=True)
    gender = models.CharField(max_length=255, blank=True, null=True)
    date_of_birth = models.DateField(blank=True, null=True)
    def __str__(self):
        return str(self.pk)

class academia_profile(models.Model):
    custom_user = models.ForeignKey(CustomUser, on_delete=models.CASCADE)
    education_or_certificate = models.CharField(max_length=255, blank=True, null=True)
    university = models.CharField(max_length=255, blank=True, null=True)
    def __str__(self):
        return str(self.pk)

class contact_profile(models.Model):
    custom_user = models.ForeignKey(CustomUser, on_delete=models.CASCADE)
    country = models.CharField(max_length=255, blank=True, null=True)
    state = models.CharField(max_length=255, blank=True, null=True)
    city = models.CharField(max_length=255, blank=True, null=True)
    def __str__(self):
        return str(self.pk)

For extracting the data of those four models, I need to extract it by querying 4 times differently and then by passsing for different variables to HTML templates it something a hectic plus would be reducing the performance speed (I am sure!)

My current queries be like

    user_base = CustomUser.objects.get(id=user_id)
    user_personal = personal_profile.objects.get(custom_user=user_id)
    academia = academia_profile.objects.get(custom_user=user_id)
    contact = contact_profile.objects.get(custom_user=user_id)

Is it possible to get all of the four queries values in a single variable by hitting a single join query in ORM ?

also, I want to extract just the country from contact_profile and picture from personal_profile in the join query.

Select_related() can able to work here but how? that's what I am not getting.

Answer 1

You are looking for prefetch_related:

Returns a QuerySet that will automatically retrieve, in a single batch, related objects for each of the specified lookups.

user_base = (
    CustomUser
    .objects
    .prefetch_related(           #<-- THIS!
        "personal_profile_set",
        "academia_profile_set",
        "contact_profile_set")
    .get(id=user_id))

personal_profile = user_base.personal_profile_set.all()[0]
academia_profile = user_base.academia_profile_set.all()[0]
contact_profile = user_base.contact_profile_set.all()[0]

Btw, if you have only one personal_profile, academia_profile, contact_profile per CustomUser, consider changing ForeignKey by OneToOneField and use select_related.