CODEWITHSUNDEEP
sqldjango
JSB
JSB

Reputation: 360

Return results from more than one database table in Django

Suppose I have 3 hypothetical models;

class State(models.Model):
    name = models.CharField(max_length=20)

class Company(models.Model):
    name = models.CharField(max_length=60)
    state = models.ForeignField(State)

class Person(models.Model):
    name = models.CharField(max_length=60)
    state = models.ForeignField(State)

I want to be able to return results in a Django app, where the results, if using SQL directly, would be based on a query such as this:

SELECT a.name as 'personName',b.name as 'companyName', b.state as 'State'
FROM Person a, Company b
WHERE a.state=b.state

I have tried using the select_related() method as suggested here, but I don't think this is quite what I am after, since I am trying to join two tables that have a common foreign-key, but have no key-relationships amongst themselves.

Any suggestions?

Upvotes: 1

Views: 79

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

Since a Person can have multiple Companys in the same state. It is not a good idea to do the JOIN at the database level. That would mean that the database will (likely) return the same Company multiple times, making the output quite large.

We can prefetch the related companies, with:

qs = Person.objects.select_related('state').prefetch_related('state__company')

Then we can query the Companys in the same state with:

for person in qs:
    print(person.state.company_set.all())

You can use a Prefetch-object [Django-doc] to prefetch the list of related companies in an attribute of the Person, for example:

from django.db.models import Prefetch

qs = Person.objects.prefetch_related(
    Prefetch('state__company', Company.objects.all(), to_attr='same_state_companies')
)

Then you can print the companies with:

for person in qs:
    print(person.same_state_companies)

Upvotes: 1

Related Questions