Hey Mike, Thanks for the quick response!
For developers that are pretty familiar with the SQLAlchemy API, but not so much the internals, would implementing the subqueryloads to contribute to SA be a reasonable endeavor? Could you ballpark how much time how long it might take for us to do it? Regarding STI and relationships, is there any way to do that but still get the benefits of JTI? e.g. is there an easy way to resolve my_base_class_inst.subclass_prop as a proxy to the subclass? We could roll our own using __getitem__ but it seems a little hairy. Thanks again, Damon On Wednesday, March 22, 2017 at 3:59:45 PM UTC-7, Mike Bayer wrote: > > > > On 03/22/2017 02:17 PM, da...@benchling.com <javascript:> 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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > 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.
