On Fri, Jun 8, 2018 at 6:59 PM, HP3 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected]. >> >> >> > To post to this group, send email to [email protected]. >> >> >> > 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 [email protected]. >> >> > To post to this group, send email to [email protected]. >> >> > 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 [email protected]. >> > To post to this group, send email to [email protected]. >> > 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
