On Fri, Jun 8, 2018 at 5:14 PM, HP3 <henddher.pedr...@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+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.