On Sep 6, 2012, at 5:07 AM, Ladislav Lenart wrote: > It works! Thank you very much for such a quick response! > > It is a pity I was unable to find it on my own though. I spent several hours > searching and reading various parts of SA documentation and on stackoverflow > - I > am a SQL / SA newbie and there is A LOT to read. But I've read select_from API > documentation now and still don't see any mention of this functionality there. > Is it somewhere? What should I have been googling for to find it quickly in > the > first place?
so, approach #1, q_cte_union.all(), didn't work ? that would be the more direct approach and follows the examples at http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.union . for approach #2 the select_from() docs don't have any examples right now to suggest the kinds of things that can go in there (which is of course, anything that represents rows). > > Thank you again, > > Ladislav Lenart > > > On 5.9.2012 18:22, Michael Bayer wrote: >> 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.
