On Wednesday, October 22, 2014 2:05:32 PM UTC-2, Michael Bayer wrote: > > > this doesn’t sound like a use case for inheritance or multiple mappings > for a single class. >
I'm aware it's not a real use case, but in principle it works for my needs. Inheritance is used just as a trick to get SQLAlchemy to query across all tables and UNION automatically. The problem is that the resulting query is not practical for big tables: For instance, if I do something like UserBase.query.filter_by(status='ACTIVE'), and I have subclasses User2013 and User2012, the query generated by sqlalchemy is something like: SELECT pjoin.* FROM (SELECT * from user_2013 UNION ALL SELECT * from user_2012) AS pjoin WHERE pjoin.status = 'ACTIVE'; And MySQL builds a temporary table with everything inside the UNION, and apply the WHERE clause to it, as a subclass. > Typically the approach is just to create copies of your mappings to a new > series of classes. An example of automating this is the “versioned objects” > example at > http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.versioned_history. > > Another way you might look into, if you’re attempting to avoid explicit > mappings, is the automap system: > http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html. > My problem isn't really automating the creation of mappings, but automating the queries on the secondary tables. For instance, today I have some code that does something like this: users = User.query.filter(User.x==1, , User.y==2, User.z==3).all() And now I need to include rows stored in other tables in that result set. I would like to be able to do something like: users = User.query.include_archive_since(date(2012, 1, 1)).filter(User.x==1, User.y==2, User.z==3).all() And the include_archive_since method would be able to figure out that it has to include User2012 and User2013 too. In other words, I'm trying to avoid the need for the code responsible for building the queries to have the knowledge of the archival structure behind everything. I already implemented this by having a proxy class that's used as User.query_class and it records all method calls. When the query is evaluated, it figures out which subclasses of User to use, builds the whole query for each one and returns the resulting union of everything. It works well, but it isn't very robust because I have to inspect the binary expressions like User.x==1, and build the equivalent User2012.x == 1, User2012.y == 2, etc, I have to reimplement a lot of stuff from Query, and so on. So, what I need is a magic function that takes the query object from: User.query.filter(User.x==1, User.y==2, User.z==3) And returns something equivalent to: User2012.query.filter(User2012.x==1, User2012.y==2, User2012.z==3) In simple terms, I need to do a search/replace on the query generated by sqlalchemy before it runs. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
