Model and code changes to get query results from a DB with multiple lookup tables-CodePudding

The Django app I am building manages client information. The short version of this question is how do I build a Django query that equates to this sql statement...

select cl.id, cl.first, cl.last, ad.zipcode, ph.phone_number, em.email_address
from client.clients as cl
   join client.addresses as ad on cl.id=ad.client_id
   join client.phones as ph on cl.id=ph.client_id
   join client.email_addresses as em on cl.id=em.client_id 
where cl.status_id=1
   and ad.type_id=1
   and ph.type_id=1
   and em.type_id=1;

...given the following models, starting with an abbreviated client:

class Client(models.Model):
    id = models.IntegerField(primary_key=True)     
    last = models.CharField(max_length=32)
    first = models.CharField(max_length=32)

The address model:

class Address(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        'Client',
        on_delete=models.DO_NOTHING,
        blank=False,
        null=False)
    type = models.ForeignKey(
        AddressType,
        on_delete=models.DO_NOTHING,
        blank=False,
        null=False)
    street = models.CharField(max_length=32, blank=True, null=True)
    city = models.CharField(max_length=32, blank=True, null=True)
    state = models.CharField(max_length=2, blank=True, null=True)
    zipcode = models.CharField(max_length=10, blank=True, null=True)

The phone model:

class Phone(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        'Client',
        on_delete=models.DO_NOTHING,
        blank=False,
        null=False)
    type_id = models.ForeignKey(
        PhoneType,        
        on_delete=models.PROTECT,
        blank=False,
        null=False)
    is_primary = models.BooleanField
    country_code = models.CharField(max_length=5)
    phone_number = models.CharField(max_length=16)

The email address model:

class EmailAddress(models.Model):
    id = models.IntegerField(primary_key=True)
    client = models.ForeignKey(
        'Client',
        on_delete=models.PROTECT,
        blank=False,
        null=False)  
    type_id = models.ForeignKey(
        EmailType,        
        on_delete=models.PROTECT,
        blank=False,
        null=False)
    email_address = models.CharField(max_length=128, blank=False, null=False)

And finally, the ClientListView that should contain the queryset:

class ClientListView(ListView):
    model = Client
    template_name = 'client/client_list.html'
    context_object_name = 'clients'

    def get_queryset(self):
        return Client.objects.order_by('-id').filter(status_id=3).select_related(Phone)

The get_queryset above doesn't come close, but ultimately, I need to get all the related data from all the lookup tables as shown in the SQL statement above, and thus far none of the combinations of select_related and prefetch_related clauses that I cobbled together have worked.

Help will be very much appreciated!

CodePudding user response：

There are many ways to get this information - it depends on how you want to access. Django documentation is a great source

Here is an example using queryset.prefetch_related() (GENERALLY THE EASIEST)

clients = Client.objects.filter(status_id=3).prefetch_related(
    "addresses",
    "phones",
    "emails",
)  # will run all queries at once here

print(clients[0].emails[0].email_address)  # will not run additional query here

Here is an example using queryset.values()

clients = Client.objects.filter(status_id=3).values(
    "id",
    "first",
    "last",
    "addressess__zip_code",
    "phones__phone_number",
    "emails__email_address"
)
print(clients[0]['emails__email_address'])

Here is an example using queryset.annotate() and "F" Expressions

clients = Client.objects.filter(status_id=3).annotate(
    zip_code=F("addressess__zip_code"),
    phone_number=F("phones__phone_number"),
    email_address=F("emails__email_address"),
)
print(clients[0].email_address)

(note - I'm not sure how dupes will work here -- can use something like postgres-specific ArrayAgg)

If all else fails, you can always write raw SQL:

clients = Client.objects.raw_sql("SELECT my_field FROM clients JOIN ...")
print(clients[0]["my_field"])