Hi 1nickt,
Until one of the of the other monks finds us a more elegant solution – try Arbitrary SQL through a custom ResultSource. See e.g. below
My::Schema
package My::Schema;
use base qw ( DBIx::Class::Schema );
# load My::Schema::Result[Set]::* classes
__PACKAGE__->load_namespaces();
1;
My::Schema::Result::CD
package My::Schema::Result::CD;
use strict;
use warnings;
use base qw ( DBIx::Class::Core );
__PACKAGE__->table_class( 'DBIx::Class::ResultSource::View' );
# specify components
__PACKAGE__->load_components( qw/ Ordered / );
__PACKAGE__->position_column( 'title' );
# specify table for Result class
__PACKAGE__->table( 'cd_view' );
# add columns to 'this' class
__PACKAGE__->add_columns( qw/ RN cdid artistid title year / );
# specify primary key(s)
__PACKAGE__->set_primary_key( qw/ cdid artistid/ );
#
# specify relationships etc..
#
# do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance
->view_definition(q[
SELECT ROW_NUMBER()
OVER (PARTITION BY artistid ORDER BY cdid) AS RN
,cdid
,artistid
,title
,year
FROM cd
]);
1;
My::Schema::ResultSet::CD
package My::Schema::ResultSet::CD;
use strict;
use warnings;
use base qw ( DBIx::Class::ResultSet );
1;
Demo
Notice the { 'RN' => { '<=', 2 } } parameter in the search call below? You would change this to 5…
#!perl -sl
use lib qw ( c:\code\perl );
use strict;
use warnings;
use My::Schema;
use constant {
DBI_DSN => 'dbi:ODBC:driver={SQL Server};Server=(local);database
+=example;Trusted Connection=yes',
DBI_USER => undef,
DBI_PASS => undef,
};
my $eg_schema = My::Schema->connect(DBI_DSN,DBI_USER,DBI_PASS,
{ RaiseError=>1, AutoCommit=> 1 });
my $cd_rs = $eg_schema->resultset('CD')
->search({ 'RN' => { '<=', 2 } });
print $_->RN.",".$_->cdid.",".$_->title while $_ = $cd_rs->next;
__END__
It'll get the job done but I wasn't able to get too creative and start using CTEs and such; your mileage may vary...
Cheers,
Shadowsong