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