[Dbix-class] Howto create Oracle WITH statements

Rob Kinyon rob.kinyon at gmail.com
Mon Mar 28 13:50:26 GMT 2011


On Mon, Mar 28, 2011 at 09:35, Dami Laurent (PJ)
<laurent.dami at justice.ge.ch> wrote:
>>-----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.

We can easily support WITH in DBIx::Class. Look at the support for
SELECT FOR UPDATE. The support is not the problem.

The reason I responded as I did was that WITH (which I've used before
in prior jobs) is often used for other reasons than CTEs. In
particular, that's not what the OP was using it for.

Rob



More information about the DBIx-Class mailing list