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.
