Django: how to query parent model based on a filter of a child model?

Question

I have those classes (simplified for sake of clarity):

class Entity(models.Model):
    adresses = models.ManyToManyField(Address,
                                      related_name='persons',
                                      through='EntityAddress')

class EntityAddress(models.Model):
    entity = models.ForeignKey(Entity, on_delete=models.CASCADE,
                               blank=False, null=False)
    address_type = models.ForeignKey(AddressType, models.CASCADE,
                                     blank=False, null=False)
    address = models.ForeignKey(Address, on_delete=models.CASCADE,
                                blank=False, null=False)

class Address(models.Model):
    summary = models.CharField(max_length=250, blank=True, null=True)
    way = PolygonField(default=None, blank=True, null=True)

class Person(Entity):
    user = models.OneToOneField(User, blank=False, null=False,
                                on_delete=models.CASCADE)

I want to have all Person's whose addresses have a "not null" way.

I do it like this:

for p in Person.objects.all():
    for e_a in EntityAddress.objects.filter(entity=p,
                                            address__way__isnull=False,):
        # do whatever here:
        pass

This is way too slow! Is there a way to make only one request?

Depending on your needs but you could add address as a field to the Person model to query it more easily. — Vincent, Sep 04 '19 at 08:54
This would break the objective = keep all "common" fields (including `adresses`) into `Entity` (I have `Company` that is child of `Entity`, for example) — Olivier Pons, Sep 04 '19 at 08:57
You can optimize the query by using the prefect_related and/or select_related methods. Read here: https://docs.djangoproject.com/en/2.2/ref/models/querysets/#prefetch-related — Vincent, Sep 04 '19 at 10:40

score 1 · Accepted Answer · edited Sep 04 '19 at 15:28

1

You could do it maybe with a nested in query?

Something like:

not_null_ea = Entity.objects.filter(addresses__way__isnull=False).values('addresses')
for p in Person.objects.filter(
        addresses__in=not_null_ea).prefetch_related('entityaddress_set):
    for a_e in p.entityaddress_set:
        pass

This way at least you would not be looping through Person objects yourself which really is quite slow.

edited Sep 04 '19 at 15:28

Olivier Pons

15,363
26
117
213

answered Sep 04 '19 at 09:08

Alper

3,424
4
39
45

`__in=[]` needs an array that is limited to a few hundred of values. I have > 1M persons... – Olivier Pons Sep 04 '19 at 09:09
>1M in `not_null_ea`? If so you might slice that queryset into chunks of a few hundred at a time, and process the matching Persons. – nigel222 Sep 04 '19 at 09:14
No, you can pass a QuerySet to `in` which will become a SQL subquery pushing all of this computation to your database. – Alper Sep 04 '19 at 09:28
@OlivierPons a queryset is not a list and it's lazy (no query happens before you try to get at the queryset results). When passing a queryset as the `__in=` argument, the ORM actually generates a subquery (ie `SELECT x FROM table1 where fk in (SELECT pk FROM table2 where some-condition-here)`). – bruno desthuilliers Sep 04 '19 at 09:42
That's what I said. – Alper Sep 04 '19 at 11:56
Does `Entity.objects.filter(addresses__way__isnull=False)` return all the entities who have at least one address not null, or all the entities who have *all* addresses not null (I need the first case)? – Olivier Pons Sep 04 '19 at 14:45
1

I've edited + checked your answer as valid because you put me on the right track. FYI, I had to add `.values('addresses')` otherwise I get `ValueError: Cannot use QuerySet for "Entity": Use a QuerySet for "Address".` – Olivier Pons Sep 04 '19 at 15:29
Cool. Yeah, I don't usually take the time to fully reproduce and validate an answer in the console. Glad it works for you! What's the speedup? – Alper Sep 04 '19 at 19:25

score 0 · Answer 2 · answered Sep 04 '19 at 09:52

0

I don't have a django project with concrete (multi-table) model inheritance at hand so I can't double-check it works, but theoretically this should do what you want:

Person.objects.filter(adresses__way__isnull=False)

This might help by the way...

answered Sep 04 '19 at 09:52

bruno desthuilliers

75,974
6
88
118

Django: how to query parent model based on a filter of a child model?

2 Answers2

Linked