I don't know what you mean. is there SQL you know you want or is that the part you want to figure out?
On Mon, Jul 1, 2019, at 4:21 PM, sector119 wrote: > Mike, is it possible to load "@property" as subquery? Maybe as > ARRAY[path_item, path_item, ...] > > воскресенье, 30 июня 2019 г., 20:26:42 UTC+3 пользователь Mike Bayer написал: >> >> >> On Sun, Jun 30, 2019, at 5:37 AM, sector119 wrote: >>> Nice, thanks a lot, Mike, now it works as expected >> >> that's great. the docs are not good here, there's not enough discussion of >> "aliased()" , from_statement() and what they do, also I'm changing some >> details of how they do their work for 1.4 in any case so documentation >> efforts will be needed. >> >> >> >>> >>> @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'*)) >>>>> 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) ** **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. >>>>> To post to this group, send email to sqlal...@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/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 sqlal...@googlegroups.com. >>> To post to this group, send email to sqlal...@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 >>> >>> <https://groups.google.com/d/msgid/sqlalchemy/fb2487d4-0765-4c58-8324-f4095f87eb82%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/e7fe32f9-5bf5-4cc9-95ee-9b4ecd1b6543%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/e7fe32f9-5bf5-4cc9-95ee-9b4ecd1b6543%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/e4d1f7cc-00b0-40c0-9c5c-fe70e656f278%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.