[dancer-users] Example of pagination with DBIC?

[John Stoffel]

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;