On Sun, Jul 7, 2019, at 9:29 AM, Olivier SAINT-EVE wrote:
> 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).
Hi, and thanks for the complete information to reproduce.
When you get an error like this, go into pdb and print out the SQL being
emitted:
(Pdb) print(sub_territories.element)
SELECT territoire_1.id, territoire_1.name, territoire_1.scale
FROM territoire AS territoire_1
WHERE territoire_1.id = :id_1 AND territoire_1.scale != :scale_1
(Pdb) print(corps)
WITH RECURSIVE sub_territories(id, name, scale) AS
(SELECT territoire_2.id AS id, territoire_2.name AS name, territoire_2.scale AS
scale
FROM territoire AS territoire_2
WHERE territoire_2.id = :id_1 AND territoire_2.scale != :scale_1)
SELECT territoire_1.id AS territoire_1_id, territoire_1.name AS
territoire_1_name, territoire_1.scale AS territoire_1_scale,
parent_child_1.child_id AS parent_child_1_child_id
FROM parent_child AS parent_child_1 JOIN territoire AS territoire_1 ON
territoire_1.id = parent_child_1.child_id JOIN sub_territories AS anon_1 ON
parent_child_1.parent_id = anon_1.id
WHERE anon_1.scale != :scale_2
Above, you can see the first query has only three columns, even though you've
placed a three-column entity plus a separate column into the Query. Why is
this? Because SELECT statements in SQLAlchemy always SELECT distinct names, and
by placing territorie_1.id in the query twice, it is deduped:
(Pdb) print(sub_territories.columns)
['sub_territories.id', 'sub_territories.name', 'sub_territories.scale']
we can see it like this also:
(Pdb) c1, c2 = column('c1'), column('c2')
(Pdb) print(select([c1, c2, c1]))
SELECT c1, c2
Since we are in the middle of changing lots of things for 2.0, I will see if
this can be adjusted because it is inconsistent that a Query(a, b, a) will
return a three-column tuple regardless, but select([a, b, a]) will return a
two-tuple: https://github.com/sqlalchemy/sqlalchemy/issues/4753
the solution for now is therefore to apply a label to the extra column:
sub_territories = (
session.query(element0, element0.id.label("child_id"))
.filter(element0.id == 1)
.filter(element0.scale != "Region")
.cte(name="sub_territories", recursive=True)
)
> 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 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/04358259-dd87-4dcb-b24a-8c81f82109f0%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/04358259-dd87-4dcb-b24a-8c81f82109f0%40googlegroups.com?utm_medium=email&utm_source=footer>.
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/e3e28207-e944-4c4f-bcd2-ec57e65d8ea3%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
