Ahhhh!!! Makes perfect sense!
But there is something still amiss ...
The last 2 assertions are triggering because the query is returning a
single ancestor (self) instead of the whole recursive list.
See the plain CTE and the @hybrid_property.expression CTE: T*he where
clauses are still different :(*
(and hence the output)
Are my @hybrid_property.expressions incorrectly declared?
CTE ----------------------------------------
2018-06-08 13:14:44,144 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-06-08 13:14:44,145 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE
cte(id, parent_id) AS
(SELECT p2bases.id AS id, p2bases.parent_id AS parent_id
FROM p2bases
WHERE p2bases.id = %(id_1)s UNION SELECT p2bases.id AS p2bases_id,
p2bases.parent_id AS p2bases_parent_id
FROM p2bases, cte
WHERE p2bases.id = cte.parent_id)
SELECT p2bases.id AS p2bases_id, p2bases.uuid AS p2bases_uuid,
p2bases.classname AS p2bases_classname, p2bases.position AS
p2bases_position, p2bases.extras AS p2bases_extras, p2bases.val AS
p2bases_val, p2bases.time AS p2bases_time, p2bases.parent_id AS
p2bases_parent_id
FROM p2bases
WHERE p2bases.id IN (SELECT cte.id AS cte_id
FROM cte)
2018-06-08 13:14:44,146 INFO sqlalchemy.engine.base.Engine {'id_1': 20}
CTE ANCESTORS <__main__.P2Pg object at 0x10bf94be0>
CTE ANCESTORS <__main__.P2An object at 0x10bf9a160>
CTE ANCESTORS <__main__.P2KVP object at 0x10bf9a6a0>
@hybrid_property.expression ANCESTORS CLS
----------------------------------------
2018-06-08 13:14:44,181 INFO sqlalchemy.engine.base.Engine 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 cte.id AS cte_id, cte.parent_id AS cte_parent_id
FROM cte
WHERE cte.id = %(id_1)s
2018-06-08 13:14:44,182 INFO sqlalchemy.engine.base.Engine {'id_1': 20}
@hybrid_property.expression ANCESTORS CLS (20, 5)
On Friday, June 8, 2018 at 12:27:28 PM UTC-5, Mike Bayer wrote:
>
> On Fri, Jun 8, 2018 at 12:16 PM, HP3 <henddher...@gmail.com <javascript:>>
> wrote:
> > Attached the whole file
>
> OK, removing all the "zope" stuff as well as all the other queries
> that aren't noted as failing, I am only looking at this query:
>
> ancestors = dbsession.query(P2B.ancestors).filter(P2B.id ==
> child_id).all() # <<<< child_id IS NEVER USED IN EXPRESSION
>
> the problem is that you are re-introducing P2B a second time, rather
> than referring to the columns that your CTE returns, it should be:
>
> ansc = P2B.ancestors
> ancestors = dbsession.query(ansc).filter(ansc.c.id == child_id).all()
>
> this produces SQL that obeys the same form I see at
> https://www.postgresql.org/docs/current/static/queries-with.html.
>
> Since P2B.ancestors produces a new selectable each time, you need to
> assign it to a variable in order to refer to its set of columns
> without re-introducing it.
>
>
>
>
> >
> >
> > On Thursday, June 7, 2018 at 7:01:21 PM UTC-5, HP3 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)
> >>
> >>
> >> 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
