Hello.
On 6.9.2012 13:04, Michael Bayer wrote: > 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). I guess I don't follow. q_cte_union.all() raises the following exception: AttributeError: 'CTE' object has no attribute 'all' My original query without select_from() works but it only returns named tuples, not actual ORM instances. I need/want to work with ORM instances because of their additional behaviour (methods). My current understanding: I want to create a recursive query and the only way to generate it is to use cte from SA Core. This construct knows nothing about ORM. Because of this, I need to select_from to map rows back to ORM instances. So if I understand you correctly, only the approach #2 works for me. Ladislav Lenart >> 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.
