On 11/16/2016 11:04 AM, Michael Williamson wrote:
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)
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.
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.
