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.
