I've managed to achieve something close to what I want by manually
setting with_polymorphic to refer to a selectable, and then assigning
columns:
column_names = ["id", "name"]
select_employees = sql.union_all(*[
sql.select(
[getattr(model, name) for name in column_names] +
[sql.literal(model.__mapper_args__["polymorphic_identity"]).label("type")]
) \
.select_from(model.__table__.join(Employee.__table__))
for model in [Manager, Engineer]
]).alias("employees")
Employee.__mapper__.with_polymorphic = ('*', select_employees)
Employee.__mapper__.polymorphic_on = select_employees.c.type
Employee.type = column_property(select_employees.c.type)
Employee.name = column_property(select_employees.c.name)
The downsides of this approach are:
* If the select doesn't cover all of the columns, the generated SQL
seems to start doing cartesian products and getting the wrong
results. I don't mind having to manually list all of the columns, but
I'd prefer an exception if I miss one (rather than a cartesian
product). Is there a way to enforce this?
* Joining onto the employee is redundant since we already have the ID
from the foreign key on the subtypes table. If there is a way to get
rid of this join, that would be great, but I can live with it if not.
(Leaving it out causes a cartesian product to be generated, as if the
IDs on the subtypes are missing.)
Full code for reference:
from __future__ import unicode_literals
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey,
sql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, column_property
Base = declarative_base()
class Employee(Base):
__tablename__ = "employee"
id = Column(Integer, primary_key=True)
class Manager(Employee):
__tablename__ = "manager"
__mapper_args__ = {
"polymorphic_identity": "manager",
}
id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
name = Column(String)
class Engineer(Employee):
__tablename__ = "engineer"
__mapper_args__ = {
"polymorphic_identity": "employee",
}
id = Column(Integer, ForeignKey(Employee.id), primary_key=True)
name = Column(String)
column_names = ["id", "name"]
select_employees = sql.union_all(*[
sql.select(
[getattr(model, name) for name in column_names] +
[sql.literal(model.__mapper_args__["polymorphic_identity"]).label("type")]
) \
.select_from(model.__table__.join(Employee.__table__))
for model in [Manager, Engineer]
]).alias("employees")
Employee.__mapper__.with_polymorphic = ('*', select_employees)
Employee.__mapper__.polymorphic_on = select_employees.c.type
Employee.type = column_property(select_employees.c.type)
Employee.name = column_property(select_employees.c.name)
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
session.add(Engineer(name="Jim"))
session.add(Manager(name="Jules"))
session.commit()
print(session.query(Employee).all())
On Wed, 16 Nov 2016 15:18:54 +0000
Michael Williamson <[email protected]> wrote:
> When using AbstractConcreteBase to implement inheritance with a
> separate table per type (and no common table), it seems like
> properties that are present on all of the subtypes are lifted up onto
> the super type. Is there a way to get the same behaviour when using
> joined table inheritance? For instance, at the moment, the below
> fails since Employee.name doesn't exist. Is there a way to get
> Employee.name to be automatically generated from the subtypes?
>
> from __future__ import unicode_literals
>
> from sqlalchemy import create_engine, Column, Integer, String,
> ForeignKey from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import Session
>
>
> Base = declarative_base()
>
>
> class Employee(Base):
> __tablename__ = "employee"
> __mapper_args__ = {
> "polymorphic_on": "discriminator",
> }
> id = Column(Integer, primary_key=True)
> discriminator = Column(String)
>
>
> class Manager(Employee):
> __tablename__ = "manager"
> __mapper_args__ = {
> "polymorphic_identity": "manager",
> }
>
> id = Column(Integer, ForeignKey(Employee.id),
> primary_key=True) name = Column(String)
>
>
> class Engineer(Employee):
> __tablename__ = "engineer"
> __mapper_args__ = {
> "polymorphic_identity": "employee",
> }
>
> id = Column(Integer, ForeignKey(Employee.id),
> primary_key=True) name = Column(String)
>
> engine = create_engine("sqlite:///:memory:", echo=True)
>
> Base.metadata.create_all(engine)
> session = Session(engine)
>
> session.add(Engineer(name="Jim"))
> session.add(Manager(name="Jules"))
> session.commit()
>
> print(session.query(Employee.name).all())
>
>
> For comparison, when using AbstractConcreteBase, it's possible to
> reference Employee.name:
>
> from __future__ import unicode_literals
>
> from sqlalchemy import create_engine, Column, Integer, String
> from sqlalchemy.ext.declarative import declarative_base,
> AbstractConcreteBase from sqlalchemy.orm import Session
>
>
> Base = declarative_base()
>
>
> class Employee(AbstractConcreteBase, Base):
> pass
>
>
> class Manager(Employee):
> __tablename__ = "manager"
> __mapper_args__ = {
> "polymorphic_identity": "manager",
> "concrete": True
> }
>
> id = Column(Integer, primary_key=True)
> name = Column(String)
>
>
> class Engineer(Employee):
> __tablename__ = "engineer"
> __mapper_args__ = {
> "polymorphic_identity": "employee",
> "concrete": True,
> }
>
> id = Column(Integer, primary_key=True)
> name = Column(String)
>
> engine = create_engine("sqlite:///:memory:", echo=True)
>
> Base.metadata.create_all(engine)
> session = Session(engine)
>
> session.add(Engineer(name="Jim"))
> session.add(Manager(name="Jules"))
> session.commit()
>
> print(session.query(Employee.name).all())
>
>
> Thanks (both in advance for this question, and for putting up with my
> other questions!)
>
> Michael
--
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.
