On Mon, Oct 30, 2017 at 6:43 PM, Tucker Beck <[email protected]> wrote:
> I wrestled through getting a model heirarchy to work with single-table
> inheritance that is polymorphic on a hybrid attribute on this mailing list a
> while ago.
>
> see: https://groups.google.com/d/topic/sqlalchemy/KJXSHwbhbLA/discussion
>
> The problem I'm running into now is that it doesn't seem to work correctly
> when I want to use the hybrid property for filtering or ordering.
> This seems to be an issue with auto-correlation, but I can't seem to figure
> out how to get it working.
>
> Here is a runnable example:
OK so lets pdb:
class HybridBeta(HybridModel):
pass
session = Session()
import pdb
pdb.set_trace()
what are we getting from HybridModel.hybrid_type_name:
(Pdb) HybridModel.hybrid_type_name
<sqlalchemy.orm.attributes.create_proxied_attribute.<locals>.Proxy
object at 0x7f95594ca200>
that's not right. I see there's a @comparator and also an
@expression. Those actually aren't designed to be used together,
you'd use one or the other. I'm not sure what you're trying to do
but based on the organization of what I see you'd want to do this:
@classmethod
def hybrid_type_name_subquery(cls):
return select([HybridType.name]).where(HybridType.id ==
cls.hybrid_type_id).as_scalar()
class HybridComparator(Comparator):
def __clause_element__(self):
return self.expression.hybrid_type_name_subquery()
def operate(self, op, other):
return op(HybridType.id,
select([HybridType.id]).where(HybridType.name == other).as_scalar())
@hybrid_type_name.comparator
def hybrid_type_name(cls):
return cls.HybridComparator(cls)
which gives you a query:
print(session.query(HybridModel).order_by(HybridModel.hybrid_type_name))
SELECT hybrids.id AS hybrids_id, hybrids.name AS hybrids_name,
hybrids.hybrid_type_id AS hybrids_hybrid_type_id, (SELECT
hybrid_types.name
FROM hybrid_types
WHERE hybrid_types.id = hybrids.hybrid_type_id) AS _sa_polymorphic_on
FROM hybrids ORDER BY (SELECT hybrid_types.name
FROM hybrid_types
WHERE hybrid_types.id = hybrids.hybrid_type_id)
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base, declared_attr
> from sqlalchemy.ext.hybrid import hybrid_property, Comparator
>
> Base = declarative_base()
>
> class classproperty(property):
> """A decorator that behaves like @property except that operates
> on classes rather than instances.
>
> The decorator is currently special when using the declarative
> module, but note that the
> :class:`~.sqlalchemy.ext.declarative.declared_attr`
> decorator should be used for this purpose with declarative.
>
> """
>
> def __init__(self, fget, *arg, **kw):
> super(classproperty, self).__init__(fget, *arg, **kw)
> self.__doc__ = fget.__doc__
>
> def __get__(desc, self, cls):
> return desc.fget(cls)
>
>
> class ModelBase(Base):
> __abstract__ = True
>
> def __repr__(self):
> return "{} ({}:{})".format(type(self).__name__, self.name, self.id)
>
>
> class HybridType(ModelBase):
> __tablename__ = 'hybrid_types'
> id = Column(Integer, primary_key=True)
> name = Column(Text)
>
>
> class HybridModel(ModelBase):
> __tablename__ = 'hybrids'
>
> id = Column(Integer, primary_key=True)
> name = Column(Text)
> hybrid_type_id = Column(Integer, ForeignKey('hybrid_types.id'),
> nullable=False)
> hybrid_type = relationship('HybridType')
>
> def __init__(self, *args, **kwargs):
> self.hybrid_type_name = self.hybrid_type_identity
> return super().__init__(*args, **kwargs)
>
> @classproperty
> def hybrid_type_identity(cls):
> return cls.__name__
>
> @declared_attr
> def __mapper_args__(cls):
> return dict(
> polymorphic_on=cls.hybrid_type_name_subquery(),
> polymorphic_identity=cls.hybrid_type_identity,
> )
>
> @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)
> )
>
> @hybrid_type_name.expression
> def hybrid_type_name(cls):
> return cls.hybrid_type_name_subquery()
>
> @classmethod
> def hybrid_type_name_subquery(cls):
> return select([HybridType.name]).where(HybridType.id ==
> cls.hybrid_type_id).as_scalar()
>
> class HybridComparator(Comparator):
>
> def operate(self, op, other):
> return op(HybridType.id,
> select([HybridType.id]).where(HybridType.name == other).as_scalar())
>
> @hybrid_type_name.comparator
> def hybrid_type_name(cls):
> return cls.HybridComparator(cls)
>
>
> class HybridAlpha(HybridModel):
> pass
>
>
> class HybridBeta(HybridModel):
> pass
>
>
> e = create_engine("sqlite://", echo=False)
> Base.metadata.create_all(e)
> session = Session(e)
>
>
> session.add(HybridType(name=HybridAlpha.hybrid_type_identity))
> session.add(HybridType(name=HybridBeta.hybrid_type_identity))
> session.add(HybridAlpha(name='alpha_instance'))
> session.add(HybridBeta(name='beta_instance'))
>
>
> print("--- Test query from base hybrid model ---")
> assert session.query(HybridModel).count() == 2
> print("passed")
> print("--- Test query from base derived hybrid model ---")
> assert session.query(HybridAlpha).count() == 1
> assert session.query(HybridBeta).count() == 1
> print("passed")
> print("--- Test query order_by on hybrid attribute ---")
> assert [
> x.hybrid_type_name for x
> in session.query(HybridModel).order_by(HybridModel.hybrid_type_name)
> ] == [HybridAlpha.hybrid_type_identity, HybridBeta.hybrid_type_identity]
> print("passed")
> print("--- Test query filter_by on hybrid attribute ---")
> assert
> session.query(HybridModel).filter_by(hybrid_type_name=HybridAlpha.hybrid_type_identity).count()
> == 1
> print("passed")
> print("--- Test query filtered on hybrid attribute ---")
> assert session.query(HybridModel).filter(HybridAlpha.hybrid_type_name ==
> HybridAlpha.hybrid_type_identity).count() == 1
> print("passed")
>
>
>
> Running this results in the following out put:
> $ python demo.py
> --- Test query from base hybrid model ---
> passed
> --- Test query from base derived hybrid model ---
> passed
> --- Test query order_by on hybrid attribute ---
> Traceback (most recent call last):
> File "demo.py", line 121, in <module>
> in session.query(HybridModel).order_by(HybridModel.hybrid_type_name)
> File "<string>", line 2, in order_by
> File
> "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/base.py",
> line 201, in generate
> fn(self, *args[1:], **kw)
> File
> "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
> line 1589, in order_by
> criterion = self._adapt_col_list(criterion)
> File
> "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
> line 256, in _adapt_col_list
> for o in cols
> File
> "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
> line 256, in <listcomp>
> for o in cols
> File
> "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
> line 4191, in _literal_as_label_reference
> return _literal_as_text(element)
> File
> "/Users/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
> line 4230, in _literal_as_text
> "instead" % type(element)
> sqlalchemy.exc.ArgumentError: SQL expression object or string expected, got
> object of type <class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>
> instead
>
> --
> 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.
--
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.
