hello!
I created this recursive query:
element0 = aliased(Territoire)
sub_territories = session.query(element0, element0.id). \
filter(element0.id == 1). \
filter(element0.scale != 'Region'). \
cte(name='sub_territories', recursive=True)
st_alias = aliased(sub_territories)
relation = aliased(parent_child)
porteur = aliased(Territoire)
corps = session.query(porteur, relation.c.child_id). \
join(porteur, porteur.id == relation.c.child_id). \
join(st_alias, relation.c.parent_id == st_alias.c.id). \
filter(st_alias.c.scale != 'Region')
sub_territories = sub_territories.union(corps)
which should give all the children of element0 recursively, stopping at the
'Region' level(items are linked by a many-to-many relation), each item
having a level which can be : world, country, region, city...
here is some code that could help you:
class Territoire(Base):
__tablename__ = 'territoire'
id = Column(Integer, primary_key=True)
name = Column(String)
scale = Column(String)
children = relationship(
'Territoire',
secondary=parent_child,
back_populates='parents',
primaryjoin=parent_child.c.parent_id == id,
secondaryjoin=parent_child.c.child_id == id,
)
parents = relationship(
'Territoire',
secondary=parent_child,
primaryjoin=parent_child.c.child_id==id,
secondaryjoin=parent_child.c.parent_id==id,
back_populates='children'
)
and the table:
parent_child = Table('parent_child', Base.metadata,
Column('parent_id', ForeignKey('territoire.id'),
primary_key=True),
Column('child_id', ForeignKey('territoire.id'),
primary_key=True))
the error message is :
sqlalchemy.exc.ArgumentError: All selectables passed to CompoundSelect must
have identical numbers of columns; select #1 has 3 columns, select #2 has 4
I don't understand this error, since the 2 CTE requests have for 1st
element a Territoire object and for 2nd element an int (id).
thank you
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/be1da9ba-c920-424a-aab7-3a5a5a7b622c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
