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
>>>
