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

15 Oct 2014

      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

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

breno