[dancer-users] Example of pagination with DBIC?

[breno]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.preshweb.co.uk/pipermail/dancer-users/attachments/20141015/9fbadec7/attachment.html>