[Dbix-class] Howto create Oracle WITH statements

[Rob Kinyon]

my $datos_meses_rs = $schema->resultset('lineas')->search({
    -and => [
        fetcha => { '>=' => '01-JUN-10' },
        fetcha => { '<' => '01-MAR-11' },
    ],
}, {
    select => [
        \'nombre AS nombre',
        \'EXTRACT(MONTH FROM fecha) AS mes',
        \'EXTRACT(YEAR FROM fecha) AS anyo',
    ],
});

my $rs = $schema->resultset('lineas')->search({}, {
    from => $datos_meses_rs->as_query,
    select => [ { count => 'nombre' }, 'mes', 'anyo' ],
    as => [ 'altas_mes', 'mes', 'anyo' ],
    group_by => [ 'anyo', 'mes' ],
    order_by => [ 'anyo', 'mes' ],
});

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

Rob

On Fri, Mar 25, 2011 at 20:49, Darren Duncan <darren at darrenduncan.net> wrote:
> Jorge Gonzalez wrote:
>>
>> WITH DATOS_MESES AS
>>      ( SELECT NOMBRE,EXTRACT(MONTH FROM FECHA) AS
>>      MES,EXTRACT(YEAR FROM FECHA) AS ANYO FROM LINEAS WHERE
>>      FECHA >= '01-JUN-10' AND FECHA < '01-MAR-11' )
>> SELECT COUNT(NOMBRE) AS ALTAS_MES,MES,ANYO FROM DATOS_MESES
>> GROUP BY ANYO,MES ORDER BY ANYO,MES ASC;
>
> Besides letting you more clearly name and format parts of your queries, the
> main advantage of WITH is when you have the same subquery you want to use
> *multiple* times in your main query (or another subquery), so you benefit
> from the refactoring out common SQL code, or if it is recursive.
>
> Since you appear to only use the subquery in one place, you can alternately
> write it like this:
>
>  SELECT COUNT(NOMBRE) AS ALTAS_MES, MES, ANYO
>    FROM (
>        SELECT NOMBRE, EXTRACT(MONTH FROM FECHA) AS MES,
>            EXTRACT(YEAR FROM FECHA) AS ANYO
>          FROM LINEAS
>          WHERE FECHA >= '01-JUN-10' AND FECHA < '01-MAR-11'
>      ) AS DATOS_MESES
>    GROUP BY ANYO, MES
>    ORDER BY ANYO, MES ASC;
>
> Of course, the WITH version is easier to read (and I prefer it where it is
> supported), even if you only use the subquery in one place, but the above
> alternative will work with more or older ORMs or DBMSs.
>
> -- Darren Duncan
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- 
Thanks,
Rob Kinyon