[dancer-users] Example of pagination with DBIC?

[Jacques Lareau]

Here is how I do it. Check the gist for better formatting.

https://gist.github.com/jacqueslareau/1e9dcc04d416e1216161

status_ok is because it's an API. You can use the value returned in a
template instead.

------------------------

get '/' => sub {

    my $rows             = params->{rows} // 10;
    my $page             = params->{page} // 1;
    my $keyword         = params->{keyword};
    my $sort_direction     = 'desc';
    my $sort_by         = 'id';

    # make sure to validate parameters

    # build your where from your parameters
    my %where;
    $where{description} = { -like => "%$keyword%" } if defined $keyword;

    my $things = rset('Project')->search(\%where, {
        page     => $page,
        rows     => $rows,
        order_by => { '-desc' . $sort_direction => $sort_by },
    });

    my @results;
    while ( my $thing = $things->next ) {
        push @results, {
            id             => $thing->id,
            description => $thing->description
        };
    }

    status_ok({
        results => \@results,
        pagination => {
            total    => $projects->pager->total_entries,
            rows     => $rows,
            page     => $page
        }
    });

}


On Wed, Oct 15, 2014 at 5:14 PM, breno <garu at cpan.org> wrote:

> Hey John!
>
> First of all, if you're doing a website search, I would recommend against
> "like" on a database - it gets really slow really fast. Instead you should
> try something made for full text search like Elasticsearch.
>
> Going back to your question: in terms of paging with Dancer and
> Dancer::Plugin::DBIC, this is what you could do:
>
> ----------------8<-----------------
> get '/search' => sub {
>      # what the user is searching: (e.g.: /search?q=whatever)
>     my $query = param('q');
>
>     # which page to render (e.g.: /search?q=whatever&page=2)
>     my $page  = param('page');
>
>     # make sure it's a valid page:
>     $page = 1 unless $page && $page =~ /^\d+$/;
>
>     # create your search parameters (I'm gonna handwave this one)
>     my %search_params = ( ... );
>
>     # fetch the proper page on the database
>     my @results = rset('MyRS')->search(
>            \%search_params,
>            {
>                 rows => 10,   # <=== how many items per page
>            }
>      )->page( $page )->all;
>
>      template 'mytemplatename', { search_results => \@results };
> };
> ---------------->8-----------------
>
> Does this help you at all? I would also read through
> https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/QuickStart.pod
> and
> https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Intro.pod
> .
>
> And of course, if you have any other questions, feel free to ask :)
>
> Cheers!
>
> garu
>
>
>
> On Wed, Oct 15, 2014 at 5:29 PM, John Stoffel <john at stoffel.org> wrote:
>
>>
>> Hi all,
>>
>> My google-fu is weak and I haven't been able to find a good example of
>> how to do pagination with Dancer, DBIC and ideally some basic
>> Javascript to make it look good.
>>
>> Here's my basic code that I'm working with right now, which I know is
>> semi busted, but I just keep hitting roadblocks.  The goal here is to
>> let me spin up quick, but nice search forms for read only mysql DBs
>> for a library I work with in my spare time.  Ideally a CRUD interface
>> down the line to allow end users to add/edit records.  DBIC looks to
>> be the way to go with this down the line, since it can do proper
>> records and transactions.  At least I think it can.
>>
>> Any way, my code:
>>
>>   package TestDBIC;
>>   use Dancer ':syntax';
>>   use Dancer::Plugin::DBIC qw(schema resultset rset);
>>
>>   # cd TestDBIC;
>>   # TestDBIC::Schema 'dbi:mysql:dbname=emina;host=quad' emina
>>
>>   our $VERSION = '0.1';
>>
>>   get '/' => sub {
>>       template 'search';
>>   };
>>
>>   post '/results' => sub {
>>     my $name = param 'Name';
>>     my $sex = param 'Sex';
>>     my $city = param 'City';
>>     my $state = param 'State';
>>     my $id = param 'MummyID';
>>
>>     my @rows;
>>     my $query;
>>     my $q = {};
>>
>>     my $limit = 10;
>>     my $opts = {};
>>     $opts->{order_by} = { -desc => 'id' };
>>     $opts->{rows} = $limit if defined $limit;
>>     # We need to join in several tables from the schema
>>     $opts->{page} = 1;
>>     $opts->{limit} = 15;
>>     $opts->{order_by} = { 'desc' => 'name'};
>>
>>     my $rs = schema->resultset('MummyInNorthAmerica');
>>
>>     if ($name) {
>>       if ($query) { $query .= " AND "; }
>>       $query .= "Name LIKE $name";
>>       $q->{name} = { like => "%$name%" } ;
>>     }
>>
>>     if ($sex) {
>>       if ($query) { $query .= " AND "; }
>>       $query .= "Sex = $sex";
>>       $q->{sex} = $sex;
>>     }
>>
>>     @rows = $rs->search($q,$opts)->pager;
>>
>>     template 'results',
>>       { query => $query,
>>         mummies => \@rows,
>>         rows => 1000
>>       };
>>   };
>>
>>   true;
>> _______________________________________________
>> dancer-users mailing list
>> dancer-users at dancer.pm
>> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
>>
>
>
> _______________________________________________
> dancer-users mailing list
> dancer-users at dancer.pm
> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.preshweb.co.uk/pipermail/dancer-users/attachments/20141015/2df89308/attachment-0001.html>