How to select from a subquery without having N+1 queries

[Luise Valentin]

Hi!

What is the best way to join on a subquery and select the records from a subquery while avoiding N+1 queries?

Going off of the example in the docs, say that I want to select Pets of relatives:

relatives = Person.select().where(Person.is_relative == True).alias("relatives_alias")

relative_pets = Pet.select(Pet, relatives).join(relatives, on=(Pet.owner == relatives.c.id))

for p in relative_pets:

   print p.name, p.owner.name

How would I do something similar to the last for loop without making an extra query for each owner?

Thanks in advance!

Luise

[Charles Leifer]

I strongly recommend reading the "Performance Techniques" page of docs, as it has a section devoted entirely to avoiding N+1 issues:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#performance-techniques

For this specific issue, you can use either `prefetch` or `aggregate_rows`:

* http://docs.peewee-orm.com/en/latest/peewee/querying.html#using-prefetch

* http://docs.peewee-orm.com/en/latest/peewee/querying.html#using-aggregate-rows

Prefetch will perform O(k) queries, so it will grab all the relatives, then grab all the pets for all the relatvies (2 queries), associating the pets with their relative in Python code.

aggregate_rows will perform 1 query that does a left outer join on the Pets. The rows are then de-duped in Python code.

I strongly suggest you profile your code, however. It is very possible that doing the O(n) queries will actually end up being faster than prefetch() or aggregate_rows() simply due to the overhead of doing the computation in Python.

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.