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