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 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/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/e40fbf53-fa8b-42c3-bbde-2c7a8e3e811e%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
