[Dbix-class] Using Database functions in DBIX::Class

[Mark West]

I am trying to use DBIx::Class in a Catalyst application to create a SQL
statement that will produce results in a crosstab list. Here is an example
of the SQL I am trying to produce.

 

select * from crosstab('

    select event_member_id, round, gross from results where event_id = 21
and round = 6 or round = 7 order by 1, 2')

    AS results(player integer, Saturday integer, Sunday integer)

;

I've attempted to create a custom method titled cross_tab that looks like
this:

 

    sub cross_tab {

    my $self = shift;

    my $attributes = shift || {};

 

    $attributes->{'select'} = "* from crosstab('

        select event_member_id, round, gross from results where

        event_id = 21 and

        round = 6 or round = 7

        order by 1, 2')";

    $attributes->{'as'} = [

        'results(player integer, Saturday integer, Sunday integer)',

    ];

 

    return $self->search({}, $attributes);

 

}

This creates SQL that looks like this:

 

SELECT me.* from crosstab('

    select event_member_id, round, gross from results where 

    event_id = 21 and 

    round = 6 or round = 7 

    order by 1, 2') 

FROM results me;

 

This is fairly close, but I don't want the "me" appended at the beginning
nor do I want to add the addition FROM statement "FROM results me". I assume
this is added when I pass the attributes to the search method.

 

Is there a better DBIx::Class method to use besides "search" or is there a
way to tell DBIx::Class not to add the additional clause?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140421/eb6005a0/attachment.htm>