Hmmm - makes sense. I'll switch to plain @property instead. Thank you very much again Mike!
On Tuesday, June 12, 2018 at 3:32:35 PM UTC-5, Mike Bayer wrote: > > On Tue, Jun 12, 2018 at 12:20 PM, HP3 <henddher...@gmail.com <javascript:>> > wrote: > > Hmmm > > > > In my testing, the class level functionality doesn't seem to be used > ever. > > Only the instance level. > > yup, because you can't do inspect(cls).session, that wouldn't have worked. > > hence safer to use @property so that your code does not imply it > implements the class-level functionality. > > > > > > I thought the class level functionality would be used if the > > @hybrid_property.expression was declared: In this case, it was the > recursive > > CTE that didn't get the `cls.id` "automagically" populated but it never > > worked. > > > > On Tuesday, June 12, 2018 at 10:19:35 AM UTC-5, Mike Bayer wrote: > >> > >> > >> > >> On Tue, Jun 12, 2018, 10:20 AM HP3 <henddher...@gmail.com> wrote: > >>> > >>> Hmmm > >>> > >>> I didn't use the @hybrid_property.expression for anything else so I > ended > >>> up not using it. > >>> > >>> However, I still use @hybrid_property ... wouldn't it be the same as > >>> @property? > >>> > >>> (I thought they were "pretty much" the same) > >> > >> > >> > >> They're quite different because the hybrid has behavior at the class > level > >> as well, that's the whole complexity of hybrids > >> > >> > >>> > >>> > >>> On Saturday, June 9, 2018 at 8:06:18 AM UTC-5, Mike Bayer wrote: > >>>> > >>>> That's fine but you should probably use a normal @property for that, > >>>> unless you are still making use of a separate "expression" portion. > >>>> > >>>> On Fri, Jun 8, 2018, 8:04 PM HP3 <henddher...@gmail.com> wrote: > >>>>> > >>>>> I ended up settling for this: > >>>>> > >>>>> @hybrid_property > >>>>> > >>>>> def ancestors(self): > >>>>> > >>>>> session = inspect(self).session > >>>>> > >>>>> cte = session.query(P2B) \ > >>>>> > >>>>> .filter(P2B.id == self.id) \ > >>>>> > >>>>> .cte(name='cte', recursive=True) > >>>>> > >>>>> cte = cte.union( > >>>>> > >>>>> session.query(P2B) > >>>>> > >>>>> .filter(P2B.id == cte.c.parent_id) > >>>>> > >>>>> ) > >>>>> > >>>>> return session.query(cte).all() > >>>>> > >>>>> > >>>>> And each time I call child.ancestors, the SQL CTE RECURSIVE is > issued > >>>>> (of course!?) > >>>>> > >>>>> Thanks again for all your help Mike! > >>>>> > >>>>> On Friday, June 8, 2018 at 6:39:05 PM UTC-5, HP3 wrote: > >>>>>> > >>>>>> 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> > 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 = <a href="http://p2bases.id"; > >>>>>>> >> >> >> >> target="_blank" rel="nofollow" > >>>>>>> >> >> >> >> onmousedown="this.href=' > http://www.google.com/url?q\x3dhttp%3A%2F%2Fp2bases.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFXb6853cCgweqpw68fbajiKFHNJg';return > > <http://www.google.com/url?q%5Cx3dhttp%3A%2F%2Fp2bases.id%5Cx26sa%5Cx3dD%5Cx26sntz%5Cx3d1%5Cx26usg%5Cx3dAFQjCNFXb6853cCgweqpw68fbajiKFHNJg';return> > > >>>>>>> >> >> >> >> true;" > >>>>>>> >> >> >> >> onclick="this.href=' > http://www.google.com/url?q\x3dhttp%3A%2F%2Fp2bases.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQj > > <http://www.google.com/url?q%5Cx3dhttp%3A%2F%2Fp2bases.id%5Cx26sa%5Cx3dD%5Cx26sntz%5Cx3d1%5Cx26usg%5Cx3dAFQj> > > >>>>> > >>>>> -- > >>>>> 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.
