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

[Mark West]

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:rabbit+dbic at rabbit.us] 
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@lists.scsys.co.uk