On Thu, Jun 7, 2018 at 8:01 PM, HP3 <henddher.pedr...@gmail.com> wrote:
> Hello
>
>
> Having difficulty with CTE and @hybrid_property.expression on a adjacency
> list model.
>
>
> class P2B(Base):
> __tablename__ = 'p2bases'
> id = Column(Integer, primary_key=True)
> classname = Column(String)
> parent_id = Column(
> Integer,
> ForeignKey('p2bases.id', ondelete='CASCADE'),
> index=True
> )
>
> parent = relationship(
> 'P2B',
> primaryjoin='P2B.parent_id == P2B.id',
> foreign_keys='P2B.id',
> uselist=False
> )
>
> __mapper_args__ = {
> 'polymorphic_identity': 'P2B',
> 'polymorphic_on': classname
> }
>
> @hybrid_property
> def ancestors(self):
> _ancestors = []
> parent = self.parent
> while parent is not None:
> _ancestors.append(parent)
> parent = parent.parent
> return _ancestors
>
> @ancestors.expression
> def ancestors(cls):
> cte = select([P2B.id, P2B.parent_id]) \
> .where(P2B.id == cls.id) \ # <<<<<< Based on Example1
> .cte(name='cte', recursive=True)
>
> cte = cte.union(
> select([P2B.id, P2B.parent_id])
> .where(P2B.id == cte.c.parent_id)
>
> )
> return cte
>
>
>
>
>
>
> The issue I am facing is that the SQL statement for ancestors expression
> becomes this
>
>
>
>
> WITH RECURSIVE cte(id, parent_id) AS
>
> (SELECT p2bases.id AS id, p2bases.parent_id AS parent_id
>
> FROM p2bases
>
> WHERE p2bases.id = p2bases.id UNION SELECT p2bases.id AS id,
> p2bases.parent_id AS parent_id
>
> FROM p2bases, cte
>
> WHERE p2bases.id = cte.parent_id)
>
> SELECT p2bases.id AS p2bases_id, p2bases.classname AS p2bases_classname,
> p2bases.position AS p2bases_position, p2bases.parent_id AS
> p2bases_parent_id,
> cte.id AS cte_id, cte.parent_id AS cte_parent_id
>
> FROM p2bases, cte
>
> WHERE p2bases.id = %(id_1)s
>
> {'id_1': 1}
>
>
>
> Notice that `P2B.id == cls.id` in the cte becomes `p2bases.id = p2bases.id`.
>
>
> What am I missing in my @hypbrid_property.expression declaration?
>
>
> How do I use my ancestors @hybrid_property.expression using
> session.query(...)?
>
> ancestors = session.query(P2B.ancestors).get(a_child_id)
the "get()" method on Query is only intended to fetch an entity, e.g.
sess.query(P2b).get(id). If you are trying to query a core
selectable, which is the case here, you'd want to do a filter() for
what you need then invoke all() or first():
sess.query(cte).filter(cte.c.id == x).first().
I would suggest working iteratively without the hybrid part first.
if you can provide a full MCVE with what you are trying and what you
expect the SQL to be I can possibly help with that.
>
>
> Example1:
> http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#correlated-subquery-relationship-hybrid
>
> --
> 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 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.
