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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, AbstractConcreteBase
Base = declarative_base()
class Employee(AbstractConcreteBase, Base):
def __init__(self, name):
self.name = name
def __repr__(self):
return self.__class__.__name__ + " " + self.name
class Manager(Employee):
__tablename__ = 'manager'
employee_id = Column(Integer, primary_key=True)
name = Column(String(50))
__mapper_args__ = {'polymorphic_identity': 'manager', 'concrete': True}
class Engineer(Employee):
__tablename__ = 'engineer'
employee_id = Column(Integer, primary_key=True)
name = Column(String(50))
__mapper_args__ = {'polymorphic_identity': 'engineer', 'concrete': True}
e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Manager('m1'),
Engineer('e1'),
Manager('m2'),
Engineer('e2'),
Manager('m3'),
Engineer('e3'),
])
s.commit()
s.close()
t = class_mapper(Employee).mapped_table
e_ = literal_column("'engineer'").label('pjoin_type')
m = literal_column("'manager'").label('pjoin_type')
e_.proxy_set.add(t.c.type)
m.proxy_set.add(t.c.type)
u = s.query(Engineer, e_).filter(Engineer.name.in_(['e2', 'e3'])).statement
u2 = s.query(Manager, m).filter(Manager.name.in_(['m2', 'm3'])).statement
u3 = u.union(u2)
q = s.query(Employee).from_statement(u3)
print q.all()
