On 03/22/2017 02:17 PM, da...@benchling.com wrote:
Hey all,
We were wondering if you had any advice on having a large (~10) number
of polymorphic subclasses for a single base class. Using
with_polymorphic: '*' causes SQLAlchemy to joinedload all subclasses
like this:
SELECT ...
FROM base_table
LEFT OUTER JOIN sub_table_1 ON base_table.id = sub_table_1.id
LEFT OUTER JOIN sub_table_2 ON base_table.id = sub_table_2.id
...
Postgres buckles under too many joins, and these queries start taking a
really long time.
One other note is that for most of our queries, only a few of these
sub-tables are actually needed, so most of the joins are wasted.
Unfortunately, ahead of time, we don't know which tables will be needed
-- we're relying on the discriminator.
Ideally, we'd be able to specify that the ORM should subqueryload the
subclasses (and only execute subqueries on the types that are present).
This would have to happen both when querying the base table, but also
when accessing relationships. We'd want it to execute a query on the
base table, then execute one query for each present subclass.
Another solution might be to use some kind of hook that
- is executed after a query returns with results (or after a list of
models are added to the session?)
- groups the models by type and runs its own subqueries to load the data
Any help here is greatly appreciated!
The purpose of with_polymorphic is more about being able to filter on
multiple classes at the same time, which is why it uses joins, but these
don't scale to many subclasses. Adding a subquery load for the
related tables would be something that the ORM can someday have as a
feature, but it would need a lot of tests to ensure it's working as
advertised.
There's a lot of ways to get those other tables loaded but none of them
look that great. Turning off with_polymorphic(), one approach is to
collect all the distinct types and identifiers from your query result;
then do a separate query for each subtype:
result = session.query(BaseClass).filter(...).all()
types = sorted([(type(obj), obj.id) for obj in result],
key=lambda t: t[0])
for type, ids in itertools.groupby(types, key=lambda t: t[0]):
session.query(type).filter(type.id.in_(ids)).all()
That will emit a query with an INNER JOIN for each class and will
populate the remaining records in the identity map. The columns that
are already loaded are not re-accessed, though the DBAPI will still send
them over the network to the cursor. You can try limiting the columns
you query for in each statement as well by using the defer() option.
Another way is to use with_polymorphic() but to provide a different kind
of SQL statement, like a polymorphic_union(). This would be a UNION of
statements that each have an inner join. the resulting SQL is a beast
but it at least isn't using those left outer joins. I think you can
probably use sqlalchemy.orm.util.polymorphic_union() directly to get
this UNION statement built up automatically.
Still another way is to reorganize the mappings to use single-table
inheritance and relationship() to link out to the related table, then
the normal "subqueryload" feature to load them as relationships. Even
though this way is ugly, I might use this (short of implementing the
related table subqueryload feature) just to make things simple.
Definitely a feature that should be added but that's not an immediate
solution.
Thanks,
Damon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
