Using Database functions in DBIX::Class

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Using Database functions in DBIX::Class

mdwestie

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?


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Using Database functions in DBIX::Class

Peter Rabbitson-2
On Mon, Apr 21, 2014 at 07:31:35PM -0400, Mark West wrote:
> I am trying to use DBIx::Class in a Catalyst application to create a SQL
> statement that will produce results in a crosstab list.

Why are you trying to use the DBIC result inflation system for this?
Create the SQL, extract it with ->as_query, massage it to taste, run it
against your $schema->storage->dbh_do and you are done...

Or am I missing something...?

Cheers


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Using Database functions in DBIX::Class

mdwestie
Thanks for the response.

To be honest, that would be a new trick for an old dog, but if it is the
best way to do it, I will give it a try.

I've made progress on this by creating a ResultSource::View per a tip in the
DBIx::Class Cookbook.

My code in the View now looks like this:

__PACKAGE__->result_source_instance->view_definition(q[
    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 call it from my controller like this:

$c->stash->{'data'} = $c->model('DB::ResultCrosstab')->search({},
$attributes);

Which creates the desired SQL:

SELECT * FROM (
    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)
) me LIMIT ?: '30'

Now, my only problem/question is how to access the data. Neither "player"
nor "Saturday" nor "Sunday" are evidently accessible.

Any suggestions?



-----Original Message-----
From: Peter Rabbitson [mailto:[hidden email]]
Sent: Tuesday, April 22, 2014 5:27 AM
To: DBIx::Class user and developer list
Subject: Re: [Dbix-class] Using Database functions in DBIX::Class

On Mon, Apr 21, 2014 at 07:31:35PM -0400, Mark West wrote:
> I am trying to use DBIx::Class in a Catalyst application to create a
> SQL statement that will produce results in a crosstab list.

Why are you trying to use the DBIC result inflation system for this?
Create the SQL, extract it with ->as_query, massage it to taste, run it
against your $schema->storage->dbh_do and you are done...

Or am I missing something...?

Cheers


_______________________________________________
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@...


_______________________________________________
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@...