On Wed, 16 Nov 2016 11:38:20 -0500
mike bayer <[email protected]> wrote:
>
>
> 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',)]
>
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?
>
>
>
>
>
>
>
> >
> > 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.
