[Dbix-class] Howto create Oracle WITH statements

[Dami Laurent (PJ)]

>-----Message d'origine-----
>De : Rob Kinyon [mailto:rob.kinyon at gmail.com]
>Envoyé : dimanche, 27. mars 2011 00:13
>À : DBIx::Class user and developer list
>Objet : Re: [Dbix-class] Howto create Oracle WITH statements


[...]

>In other words, we can easily replicate Oracle's WITH by creating our
>own subquery resultset, then reusing it as we see fit.
>
>
 

For this particular example, a subquery will do, because at the SQL level, it is indeed possible to obtain exactly the same results without the "WITH" clause.

However, for the general case, "WITH" clauses can do more than subqueries : in particular, one can use them for recursive Common Table Expressions (CTE), which gives support for querying trees, graphs, or other recursive data structures. 
See http://blogs.perl.org/users/kaare/2010/05/depth-first-nodetree.html 

But such things are hard to match to ORM concept, and to my knowledge none of the current ORMs has an appropriate solution for doing "WITH" clauses.

Laurent Dami