Re: [dancer-users] Example of pagination with DBIC?

15 Oct 2014

      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@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/Quick...
and
https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Intro...
.
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@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@dancer.pm
http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
_______________________________________________
dancer-users mailing list
dancer-users@dancer.pm
http://lists.preshweb.co.uk/mailman/listinfo/dancer-users

Re: [dancer-users] Example of pagination with DBIC?

Jacques Lareau