On 11/16/2016 11:29 AM, Michael Williamson wrote:
I have no idea what you are trying to accomplish. Why not use
joined inheritance normally? When you query for Employee, you will
get Engineer / Manager objects back, they will have .name.
If you're trying to make it so that only one SELECT is emitted in
order to get fully populated Engineer/Manager, joined inheritance
already does that as well, use plain with_polymorphic for that, the
SQL is constructed automatically.
Sorry, I haven't done a good job of explaining my use-case very well. I
want to be able to treat the name from both Engineer and Manager as the
same thing on Employee, so that I can write queries like:
session.query(Employee.name)
and also:
session.query(Employee)
So you need Employee to be with_polymorphic in all cases and you need a
.name attribute that is a column property of the coalesce of all such
name fields. See below.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity': 'employee',
'polymorphic_on': type,
'with_polymorphic': '*'
}
class Engineer(Employee):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
engineer_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity': 'engineer',
}
class Manager(Employee):
__tablename__ = 'manager'
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
manager_name = Column(String(30))
__mapper_args__ = {
'polymorphic_identity': 'manager',
}
Employee.name = func.coalesce(Engineer.engineer_name, Manager.manager_name)
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Engineer(engineer_name='e1'),
Engineer(engineer_name='e2'),
Manager(manager_name='m1'),
])
s.commit()
print s.query(Employee).filter(Employee.name.in_(['e2', 'm1'])).all()
print s.query(Employee.name).select_from(Employee).all()
query output is:
SELECT employee.id AS employee_id, employee.name AS employee_name,
employee.type AS employee_type, engineer.id AS engineer_id,
engineer.engineer_name AS engineer_engineer_name, manager.id AS
manager_id, manager.manager_name AS manager_manager_name
FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id LEFT
OUTER JOIN manager ON employee.id = manager.id
WHERE coalesce(engineer.engineer_name, manager.manager_name) IN (?, ?)
2016-11-16 11:36:26,868 INFO sqlalchemy.engine.base.Engine ('e2', 'm1')
[<__main__.Engineer object at 0x7f4dd4407c50>, <__main__.Manager object
at 0x7f4dd4407e10>]
2016-11-16 11:37:30,029 INFO sqlalchemy.engine.base.Engine SELECT
coalesce(engineer.engineer_name, manager.manager_name) AS coalesce_1
FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id LEFT
OUTER JOIN manager ON employee.id = manager.id
2016-11-16 11:37:30,029 INFO sqlalchemy.engine.base.Engine ()
[(u'e1',), (u'e2',), (u'm1',)]
Joined inheritance lets you do that if you put the name on the
employee, but I'd like to keep the name on the individual subtypes. For
my actual use case, some of the columns on one of the subtypes is
calculated from other bits of SQL: in other words, the way the columns
are written out in SQL needs to differ for each type, but I'd like to
be able to write queries to select the columns without having to
explicitly write out the union each time.
So for better motivation, imagine if Engineer.name was defined as:
@hybrid_property
def name(self):
return "E" + str(self.id)
@name.expression
def name(cls):
return ("E" + cls.id.cast(String())).label("name")
while Manager.name remains an ordinary column.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
