On 11/29/2016 07:31 PM, Tucker Beck wrote:
Thanks for this! What allows the query to filter in the derived classes
when using a column property instead of a hybrid property? Is it
declaring the __mapper_args__ in the base class declaration instead of
after it?
Yea I don't think setting __mapper_args__ after the fact on the class
does anything. Those __mapper_args__ are only for the mapper()
constructor directly, which gets called when the class is declared.
you can set the actual polymorphic_on property right now only using the
special class.__mapper__._set_polymorphic_on(<something>), that's one of
a very few setters that's available on the mapper after the fact and
even then it's not fully public API right now.
Also, it seems like having the hybrid_type_prop column property obviates
the need for the hybrid property hybrid_type_name...that is, except for
the setter. Is there a way to give a column_property a setter?
a column_property against a SQL expression is strictly a read-only
thing, since "writing" to it makes no sense in terms of persistence.
the column_property aspect here isn't strictly necessary, the mapper
will create the property internally as a special case for the
polymorphic_on setting if not provided. the example can be re-organized
w/ your hybrid in at least two ways:
class HybridModel(Base):
# ...
@declared_attr
def __mapper_args__(cls):
return {
"polymorphic_on": cls.hybrid_type_name
}
or
class HybridModel(Base):
# ...
_hybrid_name_expr = (
select([HybridType.name]).
where(HybridType.id == hybrid_type_id).
as_scalar()
)
__mapper_args__ = {
"polymorphic_on": _hybrid_name_expr
}
# ...
@hybrid_type_name.expression
def hybrid_type_name(cls):
return cls._hybrid_name_expr
On Tue, Nov 29, 2016 at 2:59 PM, mike bayer <mike...@zzzcomputing.com
<mailto:mike...@zzzcomputing.com>> wrote:
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
<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
<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
<http://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
<http://self.name>, self.id <http://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 <http://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>
<mailto: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>
<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>
<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%2bunsubscr...@googlegroups.com
<mailto:sqlalchemy%252bunsubscr...@googlegroups.com>>
<mailto:sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com>
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com
<mailto:sqlalchemy%252bunsubscr...@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>>
<mailto: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>
<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>
<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>
<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>
<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>
<mailto:sqlalchemy%2bunsubscr...@googlegroups.com
<mailto:sqlalchemy%252bunsubscr...@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>
<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>
<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
<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.
