hello,
thank you a lot for your answer.
I modified my request and now it compiles, but I do not get the expected
result...
the method should stop when it encounters a 'Territoire' which is 'region',
but it lists all the elements, even those of scale (type) 'ville' (I mean
town) which shhould be removed from the result.
here is a small code reproducing the error:
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker, aliased
from sqlalchemy.schema import Table
Base = declarative_base()
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))
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'
)
def __repr__(self):
chaine1= "<Territoire(%s // %s // %s)>" % (self.id, self.name, self.scale)
return chaine1
def __init__(self, name, scale):
self.name = name
self.scale = scale
self.parents=[]
self.children=[]
if __name__ == "__main__":
engine = create_engine('postgresql://postgres:admin@localhost/territoires2',
echo=True)
# Base = declarative_base()
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
Base.metadata.create_all(engine)
session = Session()
# print("territoire table="+Territoire.__table__)
france = Territoire(name="france", scale="pays")
metz = Territoire(name='metz', scale='ville')
grand_est = Territoire(name='Grand Est', scale='region')
metz.parents.append(grand_est)
grand_est.parents.append(france)
var = Territoire('Var', 'region')
france.children.append(var)
sete = Territoire('sete', 'ville')
var.children.append(sete)
session.add_all([france, metz, grand_est])
session.add_all([var,sete])
# +++++++++++++++++++++++
# **********************************************
session.commit()
# **********************************************
# for t in session.query(Territoire):
# print(t)
element0 = aliased(Territoire)
sub_territories = session.query(element0.id,element0.name,element0.scale). \
filter(element0.id == 1). \
filter(element0.scale != 'region'). \
cte(name='sub_territories', recursive=True)
# st_alias = aliased(sub_territories,name="sa")
relation = aliased(parent_child, name="rel")
porteur = aliased(Territoire, name="p")
rec=aliased(sub_territories,name="rec")
sub_territories = sub_territories.union(
session.query(porteur.id,porteur.name,porteur.scale). \
# filter(porteur.id == relation.c.child_id). \
filter(porteur.id == relation.c.child_id). \
filter(relation.c.parent_id == sub_territories.c.id). \
filter(sub_territories.c.scale != 'region'))
# sub_territories = sub_territories.union(corps)
print(sub_territories)
q = session.query(Territoire).select_from(sub_territories)
print('solution =' + str(q.all()))
my problem seems to be logical, I will try to solve it, but maybe someone could
help me to go faster!
thank you
Le dimanche 7 juillet 2019 15:29:19 UTC+2, Olivier SAINT-EVE a écrit :
>
> 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/abac3ac5-fac3-42f9-aa4f-1f4268b202c2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
