CODEWITHSUNDEEP
pythondjango
Jonathan
Jonathan

Reputation: 521

Stringing together a series of foreign keys to return a table value in Django

How do I select the ctype knowing the user?

models.py:

from django.contrib.auth.models import User

class CompanyType(models.Model):
    ctype = models.CharField(max_length=20)

class Company(models.Model):
    name = models.CharField(max_length=50)
    companytype = models.ForeignKey(CompanyType, on_delete=models.CASCADE)

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    company = models.ForeignKey(Company, on_delete=models.CASCADE)

I know I can do it this way, but is there a way to chain it altogether in one line?

def getcompanytype(request):
    user_id = request.user.id
    company_id = Profile.objects.get(user_id__exact=user_id).company_id
    companytype_id = Company.objects.get(id=company_id).companytype_id
    companytype = CompanyType.objects.get(id=companytype_id).ctype
    return(companytype)

essentially I want this SQL statement:

SELECT
ct.ctype
From auth_user u
left outer join Profile p
on p.user_id = u.id
left outer join Company c
on p.company_id = c.id
left outer join CompanyType ct
on ct.id = c.companytype_id
where u.id = 1 # actually current user ID, not hardcoded "1"

Upvotes: 2

Views: 30

Answers (1)

bouteillebleu
bouteillebleu

Reputation: 2493

I think you can do this by following the reverse relationships of the ForeignKeys in a filter or get call, joining the different relations together with double underscores:

CompanyType.objects.get(company__profile__user__id__exact=user_id).ctype

Although I'd suggest you try that out in a Django shell and make sure that the reverse relationships are named how you'd expect! (For example, CompanyType will have a company_set attribute that gives all the companies with a particular CompanyType; the _set shouldn't be included when you do filter queries.)

Upvotes: 1

Related Questions