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) 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 <javascript:>> > 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 <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.
