I tried the example you showed in the last post and it worked. Thank you so much!
I know this is a very uncommon use case, so I really appreciate the help. Thanks again, Ben On Tuesday, October 9, 2012 5:26:40 PM UTC-5, Michael Bayer wrote: > > > On Oct 9, 2012, at 5:42 PM, Michael Bayer wrote: > > > On Oct 9, 2012, at 2:17 PM, Benjamin Gonzalez wrote: > > > Thanks for the valuable suggestions. You are actually correct: the scheme > that they are using is sharding across tables, that's why the structure is > the same. > > And the way I was setting things up is very similar to the example you > mentioned (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName) > so that I could query multiple table names using the same class. I put > together a small example that using that and a simplified version of > Manager and Engineer. Constructing the union the way you suggested works > (the criterion goes in the subquery's WHERE clause instead of outside of > the union). > > SELECT anon_1.manager_employee_id AS anon_1_manager_employee_id, > anon_1.manager_name AS anon_1_manager_name > FROM (SELECT manager.employee_id AS manager_employee_id, manager.name AS > manager_name > FROM manager > WHERE manager.name = :name_1 UNION ALL SELECT engineer.employee_id AS > engineer_employee_id, engineer.name AS engineer_name > FROM engineer > WHERE engineer.name = :name_2) AS anon_1 > > But now the problem seems to be that SQLAlchemy will only return rows > matching the first class. > > Here's the example code. In this case there is 1 row in the manager & > engineer tables that contains the name "test". After constructing the > union, I was hoping the query would return two results but it will only > return one. However if I run count() on the query, it correctly states that > there are two rows. Am I missing something else? > > > Unfortunately what you're trying to do is nearly impossible. I spent > about an hour trying to come up with any kind of workaround; the only > workaround, which barely works, is attached. Concrete inheritance is > required since you're looking to load polymorphically, and some direct > manipulation of column expressions is needed so that the "polymorphic" > lookup works. Maybe it will be useful, though I don't know if this > approach can handle more complexity than what we have in the current "hello > world" version you see here. > > I'd need to add new ORM features and apis to make this possible (the irony > when people say we have too many features). I don't necessarily have a > plan for how this feature would be supported, though. It's kind of turning > the usual ORM loading interaction inside-out. > > > this variation also works in 0.8 without using any unofficial APIs, still > poking around with it: > > e_ = literal_column("'engineer'").label('pjoin_type') > m = literal_column("'manager'").label('pjoin_type') > > u = s.query(Engineer, e_).filter(Engineer.name.in_(['e2', 'e3'])) > u2 = s.query(Manager, m).filter(Manager.name.in_(['m2', 'm3'])) > > u = union_all(u, u2) > q = s.query(Employee).with_polymorphic('*', u, > polymorphic_on=u.c.pjoin_type).from_statement(u) > print q.all() > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/_l7oCmxKdP4J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
