On 11/29/2016 05:01 PM, Tucker Beck wrote:
The issue with using the foreign_key integer value as the discriminator
is that you won't know what that is at class declaration time. The
type_name, however, you can declare as a part of the class as you would
with a normal string discriminator. I'm not sure how you would do a
correlated subquery for the polymorphic_on attribute. I modified the
query object so that I could filter results for the derived classes. It
doesn't seem like that happens automatically. Maybe I'm just not doing
it right.
The correlated subquery will not scale as well as a direct identifier,
but here is an adaption of your test using column_property(), and you
should be able to set polymorphic_on to the select() object directly
too. There's another example of this at
http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html?highlight=polymorphic_on#sqlalchemy.orm.mapper.params.polymorphic_on.
For the "use the id" approach, you would need to query from HybridType
up front and populate the polymorphic_identity attributes after the
fact, this is a feature that is not directly supported yet but there is
a recipe to do so at
https://bitbucket.org/zzzeek/sqlalchemy/issues/2792/settable-polymorphic-identity.
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, column_property
from sqlalchemy import (
Column, Integer, ForeignKey, Text,
select, create_engine,
)
Base = declarative_base()
class HybridType(Base):
__tablename__ = 'hybrid_types'
id = Column(Integer, primary_key=True)
name = Column(Text)
class HybridModel(Base):
__tablename__ = 'hybrids'
id = Column(Integer, primary_key=True)
name = Column(Text)
hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id'))
hybrid_type = relationship('HybridType')
hybrid_type_prop = column_property(
select([HybridType.name]).
where(HybridType.id == hybrid_type_id).
as_scalar()
)
__mapper_args__ = {
"polymorphic_on": hybrid_type_prop
}
def __repr__(self):
return "{} ({}:{})".format(type(self).__name__, self.name, self.id)
def __init__(self, **kwargs):
self.hybrid_type_name =
type(self).__mapper_args__['polymorphic_identity']
super(HybridModel, self).__init__(**kwargs)
@hybrid_property
def hybrid_type_name(self):
return self.hybrid_type.name
@hybrid_type_name.setter
def hybrid_type_name(self, value):
self.hybrid_type_id = (
select([HybridType.id]).
where(HybridType.name == value)
)
class HybridAlpha(HybridModel):
__mapper_args__ = {'polymorphic_identity': 'alpha'}
class HybridBeta(HybridModel):
__mapper_args__ = {'polymorphic_identity': 'beta'}
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(HybridType(name='alpha'))
session.add(HybridType(name='beta'))
session.add(HybridAlpha(name='alpha_instance'))
session.add(HybridBeta(name='beta_instance'))
print(session.query(HybridAlpha).all())
On Tue, Nov 29, 2016 at 7:16 AM, mike bayer <mike...@zzzcomputing.com
<mailto:mike...@zzzcomputing.com>> wrote:
On 11/28/2016 05:58 PM, Tucker Beck wrote:
Hello, I'm writing today about an interesting problem we ran
into with
our sqlalchemy based data store api.
Our schema is based on the star-schema idea where we have a
large 'fact
table' with lots of rows. Within that table, each row has a
foreign key
to a small 'dimension table' in which each row has a unique
name. Thus,
the type of each row can be defined by the relationship between
the fact
row and the dimension row.
We wanted to be able to use SQLAlchemy to add some custom
functionality
for the different types of rows we have in our 'fact table'. After
learning about SQLAlchemy's inheritance models, I decided to see
if we
could support the schema that we had already devised for our
project.
The single-inheritance pattern seemed to fit the best, but I
couldn't
find a single case where someone was using a star-schema and
needed the
type discriminator to be derived from the foreign key to the
dimension
table.
Further, I found as I was digging into the mechanics of the
thing that
you could not create a row in the fact table that was typed by the
derived class at creation time. And, you cannot limit queries
from the
fact table by creating the queries against the derived classes.
Suppose
that (using declarative base) I have the fact table represented by a
model called HybridModel. This model has two derived classes
HybridAlpha
and HybridBeta. I would like to be able to create a new row in
the table
wrapped by HybridModel by calling something like
HybridAlpha(**kwargs)
and have the type of the new row reflect the inheritance model I've
described above. Next I wanted to be able to formulate a query
against
one of the derived models and have it limited by the type associated
with the derived class. So, calling something like
`session.query(HybridAlpha).all()` would only return rows with a
type
associated with the HybridAlpha model.
After a lot of tinkering and experimentation, I've come up with the
following solution:
https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f8075c75b3c89
<https://gist.github.com/dusktreader/2658dedb8ca8e2fd941f8075c75b3c89>
I would appreciate any thoughts and feedback on the matter. I'm
not sure
that my approach to this solution has been sound, and I would
appreciate
feedback.
I'd try to do things much more simply than this. Most simply, just
assign polymorphic_identity to be the integer foreign key value.
Otherwise, you should be able to do polymorphic_on on a correlated
subquery, which itself you set up as a column_property(). There
should be no need to modify Query or anything like that.
I'm out of time today but if you need more help I can try to work up
an example later on.
Thanks, and keep up the great work! SQLAlchemy is just magic!
--
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
<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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
<mailto:sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com>>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>
<mailto:sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>>.
Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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
<http://stackoverflow.com/help/mcve> for a full description.
--- You received this message because you are subscribed to a topic
in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe
<https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/unsubscribe>.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy
<https://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
-=Tucker A. Beck=-
Illustrious Writer
Devious Coder
Last Hope for the Free World
Also, Modest
--
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
