> On Oct 22, 2014, at 4:25 PM, Pedro Werneck <[email protected]> wrote: > > On Wed, Oct 22, 2014 at 6:10 PM, Michael Bayer <[email protected]> > wrote: >> >> Yeah, that's because the alternate tables have no correspondence to the >> original one, the way a SELECT would. So without building some new kind >> of core selectable that acts this way, these approaches won't work here. >> >> Based on your case that you do want a UNION that populates a collection that >> is entirely straight User objects, that does imply these would be concrete >> inheriting subclasses. > > Yes, as I said before, that was my first attempt and it works, but > generates queries with a WHERE clause is applied to the whole subquery > formed by the UNIONs, and MySQL doesn't optimize that. > > Thanks for the insights anyway. I need this done soon, so I guess I'll > keep working on my Query proxy and find a way to introspect and > rebuild the binary expressions. As a last resort I can enforce the use > of filter_by.
Yeah i think that is what you have to do, you want UNION but you want all the criteria generated on the inside. There’s two approaches to take here, either start with the UNION that polymorphic gives you, the rewrite it: SELECT u.* FROM (SELECT * FROM a UNION SELECT * FROM b) AS u WHERE u.x = y becomes -> SELECT * FROM a WHERE a.x=y UNION SELECT * FROM b WHERE b.x=y or you can catch each SELECT as they are created earlier, before you build the UNION. The first approach might be a little harder to implement but it might be more robust. SQLAlchemy does things like this all the time but they are not trivial to implement as you need to think about the whole expression. The visitor system in sqlalchemy.sql.visitors forms the basis for how operations like this are usually done, providing an interface used to scan and rebuild expressions. Whole-query rebuilds like that can be tricky, we for example do one against SQLite when we have a SELECT that has nested JOINs (see http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1, the code that does this is at https://bitbucket.org/zzzeek/sqlalchemy/src/56d5732fbdf09508784df6dc4c04e5b39ac6be85/lib/sqlalchemy/sql/compiler.py?at=master#cl-1330. -- 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.
