On Sep 5, 2012, at 9:05 AM, Ladislav Lenart wrote: > Hello. > > I have the following example (Base is the usual declarative_base instance): > > > class Node(Base): > __tablename__ = 'node' > > id = Column(Integer(), primary_key=True) > parent_id = Column(Integer(), ForeignKey('node.id')) > > def path(self, root_id): > """Query to return a list of nodes once evaluated. > It starts with the receiver node and continues upwards to the root. > It stops on a node with id root_id. > """ > q_base = session.query(Node).filter(Node.id == self.id) > q_cte = q_base.cte(name='q_cte', recursive=True) > q_cte_alias = aliased(q_cte, name='q_cte_alias') > node_alias = aliased(Node, name='node_alias') > q_rec = session.query(node_alias) > q_rec = q_rec.filter(node_alias.id == q_cte_alias.c.parent_id) > q_rec = q_rec.filter(node_alias.id != root_id) > q_cte_union = q_cte.union_all(q_rec) > q = session.query(q_cte_union) > return q > > The above query seems to do what I intend but it returns named tuples and not > Node instances. I spent all day searching SA docs but with no luck so far. > > Is this possible? If so, how?
this will probably do what you want if you either a. load the records from q_cte_union directly: q_cte_union.all() or b. you re-state the query in terms of the entity you want again: query(Node).select_from(q_cte_union).all() that is if all goes as planned...ctes and unions are pretty complex under the hood. -- 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.