[dancer-users] Example of pagination with DBIC?

[Jacques Lareau]

Please check the gist, there's errors in my reply.

On Wed, Oct 15, 2014 at 5:25 PM, Jacques Lareau <jack.lareau at gmail.com>
wrote:

> 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/fe1f8e12/attachment.html>