Thanks so much for getting back so fast, Mike!
For some background: we started with the expression, but then discovered
that the performance we were getting was pretty bad on queries when the
table was large and we were filtering by the hybrid attribute. Postgres was
doing a sequence-scan, and the plan was pretty bad. So, I learned about
custom comparators, and that helped the performance for those kind of
queries really dramatically. I didn't realize that the comparator was not
meant to be used along-side an expression; I thought they served different
purposes.
Trying your suggestion worked great for order_by. However, filtering on the
hybrid attribute is failing with complaints about correlation.
I revised the code as:
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)
)
@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)
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")
And here's the output
/venv:cem/ $ 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 ---
passed
--- Test query filter_by on hybrid attribute ---
Traceback (most recent call last):
File "demo.py", line 124, in <module>
assert
session.query(HybridModel).filter_by(hybrid_type_name=HybridAlpha.hybrid_type_identity).correlate(HybridModel).count()
== 1
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 3089, in count
return self.from_self(col).scalar()
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2843, in scalar
ret = self.one()
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2814, in one
ret = self.one_or_none()
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2784, in one_or_none
ret = list(self)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2855, in __iter__
return self._execute_and_instances(context)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/orm/query.py",
line 2878, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 945, in execute
return meth(self, multiparams, params)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/engine/base.py",
line 1046, in _execute_clauseelement
if not self.schema_for_object.is_default else None)
File "<string>", line 1, in <lambda>
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
line 436, in compile
return self._compiler(dialect, bind=bind, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/elements.py",
line 442, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 435, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 216, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 242, in process
return obj._compiler_dispatch(self, **kwargs)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py",
line 81, in _compiler_dispatch
return meth(self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1738, in visit_select
text, select, inner_columns, froms, byfrom, kwargs)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1817, in _compose_select_body
for f in froms])
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1817, in <listcomp>
for f in froms])
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py",
line 81, in _compiler_dispatch
return meth(self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1379, in visit_alias
asfrom=True, **kwargs) + \
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/annotation.py",
line 80, in _compiler_dispatch
self, visitor, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py",
line 81, in _compiler_dispatch
return meth(self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1716, in visit_select
for name, column in select._columns_plus_names
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1716, in <listcomp>
for name, column in select._columns_plus_names
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1488, in _label_select_column
**column_clause_args
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py",
line 81, in _compiler_dispatch
return meth(self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 664, in visit_label
OPERATORS[operators.as_] + \
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py",
line 81, in _compiler_dispatch
return meth(self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 576, in visit_grouping
return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/visitors.py",
line 81, in _compiler_dispatch
return meth(self, **kw)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1685, in visit_select
froms = self._setup_select_stack(select, entry, asfrom, lateral)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/compiler.py",
line 1788, in _setup_select_stack
implicit_correlate_froms=asfrom_froms)
File
"/home/tbeck/.virtualenvs/cem/lib/python3.5/site-packages/sqlalchemy/sql/selectable.py",
line 2843, in _get_display_froms
"manually." % self)
sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT
hybrid_types.name
FROM hybrid_types, hybrids
WHERE hybrid_types.id = hybrids.hybrid_type_id' returned no FROM clauses
due to auto-correlation; specify correlate(<tables>) to control correlation
manually.
I've tried looking for some examples for how to use correlate and tried
stuffing it in a few places and still haven't gotten around this error.
What am I missing?
(Thanks again so much for the quick response)
On Mon, Oct 30, 2017 at 4:58 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
> On Mon, Oct 30, 2017 at 6:43 PM, Tucker Beck <tucker.b...@gmail.com>
> 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 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.
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/sqlalchemy/M4b5y_d69u0/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>
--
-=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.
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.
