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