On 17.9.2012 14:54, Michael Bayer wrote:
>
> On Sep 17, 2012, at 7:19 AM, Ladislav Lenart wrote:
>
>> Hello.
>>
>> Imagine the following ORM definition in PostgreSQL:
>>
>> class Node(Base):
>> __tablename__ = 'node'
>> id = Column(Integer(), primary_key=True)
>> parent_id = Column(Integer(), ForeignKey('node.id'))
>> # more data...
>>
>> with the following query method:
>>
>> def path_to_root(self, root_id=None, load_all=True):
>> cls = self.__class__
>> q_base = session.query(cls).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')
>> q_rec = session.query(cls_alias)
>> 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.options(joinedload_all(cls.foo, Foo.bar)) # <-- marked line
>> return q
>>
>> It should return path from the receiver node upwards to the root.
>>
>> The query works as expected without the marked line. However the order of
>> result
>> items is reversed when the marked line is present. I was under the impression
>> that joinedload option should not have observable side-effects.
>>
>> Was I wrong?
>
> no, however I see something very conspicuously missing from your query above
> with regard to ordering, which is an "order_by". You can never rely on the
> ordering of results from a relational database if ORDER BY is not explicitly
> present in the query. The means by which the database creates a query plan
> out of the given clauses and executes them can change radically when extra
> joins are added, thereby affecting the "natural" ordering of results.
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.
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?
Thanks,
Ladislav Lenart
--
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.
