Nice, thanks a lot, Mike, now it works as expected @property def path(self): session = object_session(self)
def get_locality_path_q(locality_id): parent_q = session.query(Locality).filter(Locality.id == locality_id).cte(recursive=True) parents = aliased(parent_q) locality_alias = aliased(Locality) q = parent_q.union_all( session.query(locality_alias).join(parents, locality_alias.id == parents.c.parent_id) ) cte = aliased(Locality, q) return session.query(cte).order_by(cte.id) return get_locality_path_q(self.id) воскресенье, 30 июня 2019 г., 1:11:21 UTC+3 пользователь Mike Bayer написал: > > > > On Sat, Jun 29, 2019, at 11:24 AM, sector119 wrote: > > Hello, > > > I have Locality model with 'path' property to get path from 'root' of tree to > current item, everything works ok, but > > I can't get result as Locality instance list.. > > When I use > *'*object_session(self).query(Locality).from_statement(q).order_by(Locality.id)' > I get > > sqlalchemy.exc.ArgumentError: from_statement accepts text(), select(), and > union() objects only. > > > How can I adopt results to Locality model? > > > > *class *Locality(Base): > __tablename__ = > *'localities'* > __table_args__ = {*'schema'*: SYSTEM_SCHEMA} > > id = Column(Integer, primary_key=*True*) > parent_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.localities.id > <http://localities.id>'*)) > name = Column(UnicodeText, nullable=*False*) > type = Column(Integer, nullable=*False*) > > @property > *def *path(self): > *def *get_locality_path_q(locality_id): > top_q = select([ > Locality.id, > Locality.parent_id, > Locality.name, > Locality.type, > ]).\ > where(Locality.id == locality_id).\ > cte(recursive=*True*) > > parents = aliased(top_q) > > locality_alias = aliased(Locality) > > q = top_q.union_all( > select([ > locality_alias.id, > locality_alias.parent_id, > locality_alias.name, > locality_alias.type > ]).select_from(join(locality_alias, parents, > locality_alias.id == parents.c.parent_id)) > ) > > > *# return object_session(self).query(q).order_by(q.c.id <http://q.c.id>)* > *return > *object_session(self).query(Locality).from_statement(q).order_by(Locality.id) > > *return *get_locality_path_q(self.id) > > > above, "q" is a CTE, not a SELECT, meaning it's something you can select > FROM, like a table. Call select() to SELECT from it: > > return ( > object_session(self) > .query(Locality) > .from_statement(q.select().order_by(q.c.id)) > ) > > > additionally, from_statement() does not allow further changes to the > statement and the ORDER BY must be in terms of thing you are selecting > from, in this case q.c.id > > your code would be easier if you made use of top_q as a FROM object rather > than a statement: > > lcte = aliased(Locality, q) > > return ( > object_session(self) > .query(lcte) > .order_by(lcte.id) > ) > > > There's not too many doc examples of how aliased() works with FROM clauses > but the basic idea is at: > > > https://docs.sqlalchemy.org/en/13/orm/tutorial.html#selecting-entities-from-subqueries > > https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.aliased > > A little more on aliased() with CTE is written more for query.cte() which > you could also be using here: > > > https://docs.sqlalchemy.org/en/13/orm/query.html?highlight=cte#sqlalchemy.orm.query.Query.cte > > > > > > > -- > 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 sqlal...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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/78205703-4631-4c1a-aa13-f89fc6f06feb%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/78205703-4631-4c1a-aa13-f89fc6f06feb%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/fb2487d4-0765-4c58-8324-f4095f87eb82%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.