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.
