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.
OK, heh, this will work in 0.7 or 0.8, just the argument name was wrong:
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, u.c.pjoin_type).from_statement(u)
print q.all()
getting closer !
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
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.
