Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re: Get top N rows in each group with DBIx::Class

by shadowsong (Monk)
on Nov 26, 2017 at 18:30 UTC ( #1204290=note: print w/replies, xml ) Need Help??


in reply to Get top N rows in each group with DBIx::Class

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

Replies are listed 'Best First'.
Re^2: Get top N rows in each group with DBIx::Class
by 1nickt (Abbot) on Nov 26, 2017 at 20:22 UTC

    Hi Shadowsong, thanks very much for your reply and demo. I had seen that part of the doc and it looks promising, but the problem I am facing is that this filter ('N per "group"') must be applied after some other search calls on the RS.

    Continuing the metaphor, this would be something like:

    1. Restrict Artists to given musical genre
    2. Restrict Albums to those release since given date
    3. Restrict results to maximum N Albums per Artist
    In my app I have the first two steps handled with methods in My::Schema::ResultSet::CD:
    sub by_genre { $_[0]->search({ 'me.genre' => $_[1] }); sub since { $_[0]->search({ 'me.released' => { '>=', $_[1] } });
    So the consuming code can do:
    my $rs = $schema->resultset('CD'); if ( my $genre = param->{'genre'} ) { $rs = $rs->by_genre( $genre ); } if ( my $since = param->{'since'} ) { $rs = $rs->since( $since ); }
    I need to add a method that can act upon the existing ResultSet,
    if ( my $limit = param->{'limit'} ) { $rs = $rs->limit_per_artist( $limit ); }
    It's not clear to me that the doc you linked to and demoed can be adapted to search an existing RS, but I'll keep experimenting. Thanks again.


    The way forward always starts with a minimal test.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1204290]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2019-05-24 06:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you enjoy 3D movies?



    Results (147 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!