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 <[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 = <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 [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.
