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.
