BTW, the solution of `.params(child_id=child_id)` you provided worked perfectly - forgot to mentioned earlier. It issued the recursive CTE I wanted.
I still need to ponder if I can simply live with a plain select().cte() within a vanilla method or @hybrid_property or @hybrid_method, all except @hybrid_property.expression as in the latter the "caller" would need to remember to bind `child_id` explicitly and forfeit the usage of `filter(ancs.c.id==child_id)` Thank you very much again Mike !!!! On Friday, June 8, 2018 at 6:06:54 PM UTC-5, Mike Bayer wrote: > > On Fri, Jun 8, 2018 at 6:59 PM, HP3 <henddher...@gmail.com <javascript:>> > wrote: > > :( > > > > Basically, I was trying to prevent the SQL Select for each > child-to-parent > > loop-iteration when navigating the tree from leaf to root because I > figured > > I could use CTE recursive as @hybrid_property.expression. > > > > I was trying to take full advantage of @hybrid_property.expression like > the > > examples in > > > http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#join-dependent-relationship-hybrid > > > and > > > http://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html#module-sqlalchemy.ext.hybrid > > > The examples show that self/cls is magically chosen depending on the > > context. > > > > Seems like I will end up having *limited* functionality for my > > `@hybrid_property.expression` as I would have to pass the extra param > > `cls_id` explicitly. > > > > I was hoping that > `dbsession.query(P2B.ancestors).filter(P2B.id==mychildid)` > > would automagically bind 'mychildid' to `cls.id` within the > > @ancestors.expression. > > Perhaps such is possible some other way? > > a hybrid is just a function to give you a component of SQL. if you > want to change the inside of it, then you need to send a parameter on > the inside or do a transformation of it. I guess you could run a > transform, which is what the lazy loader does, e.g. looks for where > something like "cls.id" is and then replaces it but this is very > specialized, it wouldn't look any nicer from the calling point of > view. > > > > > > > > Another catch is that by issuing the manual CTE, the session would > always > > issue the SQL and never returned previously loaded ancestors. > > > > root > > / \ > > c1 c2 > > > > c1.ancestors and c2.ancestors will each issue SQL recursive CTE ... > right? > > > > > > On Friday, June 8, 2018 at 5:33:08 PM UTC-5, Mike Bayer wrote: > >> > >> On Fri, Jun 8, 2018 at 5:14 PM, HP3 <henddher...@gmail.com> wrote: > >> > Thank you so much Mike > >> > > >> > > >> > I am trying to write @ancestors.expression to produce a SQL like > this: > >> > > >> > WITH RECURSIVE scte(id, parent_id) AS > >> > > >> > (SELECT p2bases.id AS id, p2bases.parent_id AS parent_id > >> > > >> > FROM p2bases > >> > > >> > WHERE p2bases.id = %(id_1)s --- This is the param I need at runtime > >> > (cls.id) > >> > > >> > UNION SELECT p2bases.id AS id, p2bases.parent_id AS parent_id > >> > > >> > FROM p2bases, scte > >> > > >> > WHERE p2bases.id = scte.parent_id) > >> > > >> > SELECT scte.id > >> > > >> > FROM scte > >> > >> > >> you'd have to put the parameter inside the CTE then, here's one way: > >> > >> @ancestors.expression > >> def ancestors(cls): > >> cte = select([P2B.id, P2B.parent_id]) \ > >> .where(P2B.id == bindparam("cls_id")) \ > >> .cte(name='cte', recursive=True) > >> cte = cte.union( > >> select([P2B.id, P2B.parent_id]) > >> .where(P2B.id == cte.c.parent_id) > >> ) > >> return cte > >> > >> > >> then you do: > >> > >> dbsession.query(P2B.ancestors).params(cls_id=child_id).all() > >> > >> > >> but note that you always need to provide params(cls_id=x) in that case. > >> > >> > >> > >> > > >> > > >> > > >> > > >> > Here is the @hybrid_property.expression > >> > > >> > @ancestors.expression > >> > > >> > def ancestors(cls): > >> > > >> > cte = select([P2B.id, P2B.parent_id]) \ > >> > > >> > .where(P2B.id == cls.id) \ # <<<<<<< I cannot figure out > how > >> > make cls.id the param > >> > > >> > .cte(name='cte', recursive=True) > >> > > >> > cte = cte.union( > >> > > >> > select([P2B.id, P2B.parent_id]) > >> > > >> > .where(P2B.id == cte.c.parent_id) > >> > > >> > ) > >> > > >> > return cte > >> > > >> > > >> > I tried alias, aliased and bindparam in multiple ways unsuccessfully. > >> > > >> > > >> > The SQL that is coming out doesn't set the first (cte anchor) WHERE > >> > clause > >> > with the bound param id_1 (cls.id in the @ancestors.expression). > >> > Instead, is the column again? > >> > > >> > 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 -- <<<< I can't figure out how to make > >> > expr.left 'cls.id' from the @ancestors.expression > >> > > >> > 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 > >> > > >> > > >> > > >> > The correct SQL comes out when I do this: > >> > > >> > scte = select([P2B.id, P2B.parent_id]) \ > >> > > >> > .where(P2B.id == child_id) \ > >> > > >> > .cte(name='scte', recursive=True) > >> > > >> > scte = scte.union( > >> > > >> > select([P2B.id, P2B.parent_id]).where(P2B.id == > >> > scte.c.parent_id)) > >> > > >> > s = select([scte.c.id]) > >> > > >> > ancestors = dbsession.execute(s).fetchall() > >> > > >> > > >> > But I don't know how to integrate that into my @ancestors.expression > >> > > >> > I want `child_id` to get the value of `cls.id` when the > @hybrid_property > >> > is > >> > called for the class. > >> > > >> > > >> > > >> > > >> > On Friday, June 8, 2018 at 1:48:55 PM UTC-5, Mike Bayer wrote: > >> >> > >> >> the example is huge can you point out which two queries you'd like > to > >> >> compare? > >> >> > >> >> this: > >> >> cte = dbsession.query(P2B.id, P2B.parent_id) \ > >> >> .filter(P2B.id == child_id) \ > >> >> .cte(name='cte', recursive=True) > >> >> cte = cte.union( > >> >> dbsession.query(P2B.id, P2B.parent_id).filter( > >> >> P2B.id == cte.c.parent_id)) > >> >> cteids = dbsession.query(cte.c.id) > >> >> ancestors = > >> >> dbsession.query(P2B).filter(P2B.id.in_(cteids)).all()[1:] > >> >> > >> >> > >> >> and this? > >> >> > >> >> ancestors = dbsession.query( > >> >> P2B.ancestors).filter(P2B.id == child_id).all() # <<<< > >> >> child_id IS NEVER USED IN EXPRESSION > >> >> > >> >> > >> >> > >> >> it might surprise you to know I barely follow what these queries are > >> >> returning for you, I just look very briefly to get the desired > syntax > >> >> right, so a simple "here's teh code hre's the SQL" is best > >> >> > >> >> > >> >> > >> >> On Fri, Jun 8, 2018 at 2:44 PM, HP3 <henddher...@gmail.com> wrote: > >> >> > 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: The > 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> > 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. > >> >> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com. > >> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com. > >> > To post to this group, send email to sqlal...@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+...@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.
