Python, Django: Query on combined models?

Question

inside my app I have multiple models, like:

models.py:

class Company(models.Model):
    name = models.CharField(max_length=100)


class Coworker(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    company = models.ForeignKey(Company, null=False, blank=False, on_delete=models.CASCADE)

As you can see, a Company can contain one, multiple or no Coworker! Is it possible to query Company but also receive the data from connected Coworker? For example something like this:

id    |    Company    |    Coworker
1     |    Company_A  |    Coworker_A
2     |    Company_B  |    
3     |    Company_C  |    Coworker_B
4     |    Company_C  |    Coworker_C
5     |    Company_C  |    Coworker_D
6     |    Company_D  |    Coworker_E
7     |    Company_D  |    Coworker_F
8     |    Company_E  |    
9     |    Company_F  |    Coworker_G
10    |    Company_F  |    Coworker_H
...

My problem is, that I can't query on the Coworker, because I don't want to miss those Companies that have no related data!

Thanks for your help and have a great day!

Use the filter method, if there is no coworker for that company it will return empty list [] — Luiz
– Luiz, Commented Dec 9, 2021 at 15:13

ticster · Accepted Answer · 2021-12-09 15:53:41Z

1

Quite simply, query by company and prefetch results for workers :

Company.objects.prefetch_related("coworker_set").all()

What this will do is give you a list of companies containing their list of workers in the attribute coworker_set. It will also populate those attributes in a single query (that's the whole point of prefetch_related).

More specifically, here is how you could use such a query :

for company in Company.objects.prefetch_related("coworker_set").all():
    print(company.name)
    for coworker in company.coworker_set.all():
        print(coworker.first_name)
        print(coworker.last_name)

This guarantees that you will iterate through all companies as well as through all coworkers, and you will only iterate through each one once (indeed, if you queried by coworkers you would see some companies multiple times and others none at all).

edited Dec 9, 2021 at 15:53

answered Dec 9, 2021 at 15:26

ticster

7862 gold badges6 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

finethen Over a year ago

Thank your very much, especially for the extra content! Helped perfectly!

Collectives™ on Stack Overflow

Python, Django: Query on combined models?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related