On 11/16/2016 11:58 AM, Michael Williamson wrote:
Thanks, that seems to get most of the way there. The only problem is
that calling .name on the instance returns None:
print s.query(Employee).select_from(Employee).first().name
It seems like getting rid of Employee.name and renaming manager_name
and engineer_name to just name works -- is there a reason that's a bad
idea?
whoops, forgot the column_property():
Employee.name = column_property(func.coalesce(Engineer.engineer_name,
Manager.manager_name))
the select_from() isn't needed either, I was wondering why it needed that:
print s.query(Employee).filter(Employee.name.in_(['e2', 'm1'])).all()
print s.query(Employee.name).all()
print s.query(Employee).select_from(Employee).first().name
output:
SELECT employee.id AS employee_id, coalesce(engineer.engineer_name,
manager.manager_name) AS coalesce_1, 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 12:01:38,230 INFO sqlalchemy.engine.base.Engine ('e2', 'm1')
[<__main__.Engineer object at 0x7fc9acf8de90>, <__main__.Manager object
at 0x7fc9acf20050>]
2016-11-16 12:01:38,232 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 12:01:38,232 INFO sqlalchemy.engine.base.Engine ()
[(u'e1',), (u'e2',), (u'm1',)]
2016-11-16 12:01:38,234 INFO sqlalchemy.engine.base.Engine SELECT
employee.id AS employee_id, coalesce(engineer.engineer_name,
manager.manager_name) AS coalesce_1, 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
LIMIT ? OFFSET ?
2016-11-16 12:01:38,234 INFO sqlalchemy.engine.base.Engine (1, 0)
e1
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.
