CODEWITHSUNDEEP
django
Carlos Souza
Carlos Souza

Reputation: 11

Django with filtered join

I have an inner join:

SELECT c.name, b.name FROM company c
INNER JOIN branch b ON b.company_id = c.id
WHERE c.id = 5 AND b.employees > 10;

That I got 3 registers.

How do I make this query in Django to returns only 3 registers?

c = Company.objects.prefetch_related("comp_branches").filter(id=5, comp_branches__employees__gt=10)
c[0].comp_branches.all()  # returns 5 registers

Upvotes: 1

Views: 38

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476699

You can use a Prefetch object [Django-doc]:

from django.db.models import Prefetch

c = Company.objects.prefetch_related(
    Prefetch('comp_branches', Branch.objects.filter(employees__gte=10))
).filter(id=5)

Here Brach is the model that is targeted by comp_branches, this thus might be different.

If you .filter(), you do not filter on the related objects you are prefetching, since that is done in a separate query. The comp_brances__employees__gte=10 would only remove Companys from the QuerySet that have no brach with 10 or more employees.

Upvotes: 1

Related Questions