Hello.
Just to let you know that I figured it out. It was a straightforward addition.
Here is the complete recursive query with added order_by clause (for future
reference):
def path_to_root(self, root_id=None, load_all=True):
cls = self.__class__
i0 = literal_column('0').label('iteration')
q_base = session.query(cls, i0).filter(cls.id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
cls_alias = aliased(cls, name='cls_alias')
i1 = literal_column('iteration + 1').label('iteration')
q_rec = session.query(cls_alias, i1)
q_rec = q_rec.filter(cls_alias.id == q_cte_alias.c.parent_id)
q_rec = q_rec.filter(cls_alias.id != root_id)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.order_by(q_cte_union.c.iteration)
q = q.options(joinedload_all(cls.foo, Foo.bar))
return q
Thank you again for your patient guidance,
Ladislav Lenart
On 18.9.2012 15:28, Ladislav Lenart wrote:
> Ok, thank you.
>
> I know about
>
>> SELECT a, b FROM x ORDER BY c
>
> I just don't know how to write it because I (think I) need to define the
> literal
> column as one of the CTE columns. I have to increment it in each iteration. I
> will play with it for a while and see where it will lead me to.
>
> Thank you again for your time,
>
> Ladislav Lenart
>
>
> On 17.9.2012 16:05, Michael Bayer wrote:
>>
>> On Sep 17, 2012, at 9:23 AM, Ladislav Lenart wrote:
>>
>>>
>>> Hm, I see. My train of thought: This particular query returns the path from
>>> a
>>> selected node to the root. Each iteration adds one row to the result set
>>> (one
>>> new parent). I thought that UNION ALL keeps the order intact and thus no
>>> order_by clause is needed. I guess I was wrong.
>>
>> nothing except ORDER BY orders rows in SQL. All the rest is an artifact of
>> how the query executes.
>>
>>>
>>> I can add a depth/iteration column (via literal_column) and order_by it.
>>> However
>>> I still want the results to be a list of Node instances (i.e. without the
>>> added
>>> depth column). How can I write such a query?
>>
>> you can order_by() any expression, and that expression does not need to be
>> in the columns clause. That is:
>>
>> SELECT a, b FROM x ORDER BY c
>>
>>
>> is just as valid as
>>
>> SELECT a, b, c FROM x ORDER BY c
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
