[dancer-users] Example of pagination with DBIC?

[John Stoffel]

breno> First of all, if you're doing a website search, I would
breno> recommend against "like" on a database - it gets really slow
breno> really fast. Instead you should try something made for full
breno> text search like Elasticsearch.

Sure, I know it's probably slow but it's a small database (under 1000
entries, though closer to 3000 owners listed.  Again, not huge.  I do
appreciate the pointer and I'll probably play with it down the line.  

breno> Going back to your question: in terms of paging with Dancer and
breno> Dancer::Plugin::DBIC, this is what you could do:

Thanks for this, I'm going to have to look at this more closely.  The
only issue I see is that the 'get' might get *really* hairy, since the
users can search on multiple fields at a time (though I doubt in
practice this happens... but you never know!).  

After reading up more on DBIx::Class stuff, I really need to go back
into the DB and cleanup the schema and the naming.  It's really
horrible and crufty.  But again, that's for another day.  

Just to give people a hint of what I'm doing, here's my search.tt
template:

   # TestDBIC/views/search.tt

   <h2>Search for Mummies</h2>
   <p>
   <FORM ACTION="/results" METHOD="post">
     Search Name: <input type="text" name="Name" /><br>
     Sex: <SELECT NAME="Sex">
       <OPTION>
       <OPTION VALUE="F">Female</OPTION>
       <OPTION VALUE="M">Male</OPTION>
       <OPTION VALUE="U">Unknown</OPTION>
     </SELECT>
     City: <input type="text" name="City"/><br>
     State: <input type="text" name="State"/><br>
     <INPUT type="submit" name="submit" value="Search" />
   </form>
   </p>
   <br>  

and of course my results.tt as well.

   <% IF query.length %>
     <p>Search query was : <% query %></p>
     <HR>
     <% IF mummies.size %>
       Found <% mummies.size (num) %> Records:
       <table COLSPAN=6>
       <% FOREACH mummy IN mummies %>
	 <TR> 
	   <TD COLSPAN=1> Name:</TD> <TD>
	   <TD COLSPAN=3> <% mummy.name %></TD>
	   <TD COLSPAN=2> MummyID = <% mummy.mummyid %> </TD>
	   <TD COLSPAN=1> Sex: <% mummy.sex %> </TD>
	 </TR>
	 <TR>
	   <TD COLSPAN=6> <% mummy.Notes %>
	 </TR><BR>
       <% END %>
       </TABLE>
     <% ELSE %>
       No Mummies found for your search.
     <% END %>
   <% ELSE %>
     <p>Search query was empty...</P>
   <% END %>
   <P><A HREF="<%url_for("search")%>">Search</A>
  
All of this is just quick throw together hacks for now, until I get
the basics working.  As you can see, I was following the instructions
that included using the bootstrap or twitter CSS style stuff.  Which
of course isn't setup properly.  Heh.

Thanks!
John


breno> ----------------8<-----------------
breno> get '/search' => sub {
breno>      # what the user is searching: (e.g.: /search?q=whatever)
breno>     my $query = param('q');

breno>     # which page to render (e.g.: /search?q=whatever&page=2)
breno>     my $page  = param('page');

breno>     # make sure it's a valid page:
breno>     $page = 1 unless $page && $page =~ /^\d+$/;

breno>     # create your search parameters (I'm gonna handwave this one)
breno>     my %search_params = ( ... );

breno>     # fetch the proper page on the database
breno>     my @results = rset('MyRS')->search(
breno>            \%search_params,
breno>            {
breno>                 rows => 10,   # <=== how many items per page
breno>            }
breno>      )->page( $page )->all;

breno>      template 'mytemplatename', { search_results => \@results };
breno> };
----------------> 8-----------------

breno> Does this help you at all? I would also read through https://metacpan.org/pod/distribution/
breno> DBIx-Class/lib/DBIx/Class/Manual/QuickStart.pod and https://metacpan.org/pod/distribution/
breno> DBIx-Class/lib/DBIx/Class/Manual/Intro.pod.

breno> And of course, if you have any other questions, feel free to ask :)

breno> Cheers!

breno> garu

breno> On Wed, Oct 15, 2014 at 5:29 PM, John Stoffel <john at stoffel.org> wrote:

breno>     Hi all,
   
breno>     My google-fu is weak and I haven't been able to find a good example of
breno>     how to do pagination with Dancer, DBIC and ideally some basic
breno>     Javascript to make it look good.
   
breno>     Here's my basic code that I'm working with right now, which I know is
breno>     semi busted, but I just keep hitting roadblocks.  The goal here is to
breno>     let me spin up quick, but nice search forms for read only mysql DBs
breno>     for a library I work with in my spare time.  Ideally a CRUD interface
breno>     down the line to allow end users to add/edit records.  DBIC looks to
breno>     be the way to go with this down the line, since it can do proper
breno>     records and transactions.  At least I think it can.
   
breno>     Any way, my code:
   
breno>       package TestDBIC;
breno>       use Dancer ':syntax';
breno>       use Dancer::Plugin::DBIC qw(schema resultset rset);
   
breno>       # cd TestDBIC;
breno>       # TestDBIC::Schema 'dbi:mysql:dbname=emina;host=quad' emina
   
breno>       our $VERSION = '0.1';
   
breno>       get '/' => sub {
breno>           template 'search';
breno>       };
   
breno>       post '/results' => sub {
breno>         my $name = param 'Name';
breno>         my $sex = param 'Sex';
breno>         my $city = param 'City';
breno>         my $state = param 'State';
breno>         my $id = param 'MummyID';
   
breno>         my @rows;
breno>         my $query;
breno>         my $q = {};
   
breno>         my $limit = 10;
breno>         my $opts = {};
breno>         $opts->{order_by} = { -desc => 'id' };
breno>         $opts->{rows} = $limit if defined $limit;
breno>         # We need to join in several tables from the schema
breno>         $opts->{page} = 1;
breno>         $opts->{limit} = 15;
breno>         $opts->{order_by} = { 'desc' => 'name'};
   
breno>         my $rs = schema->resultset('MummyInNorthAmerica');
   
breno>         if ($name) {
breno>           if ($query) { $query .= " AND "; }
breno>           $query .= "Name LIKE $name";
breno>           $q->{name} = { like => "%$name%" } ;
breno>         }
   
breno>         if ($sex) {
breno>           if ($query) { $query .= " AND "; }
breno>           $query .= "Sex = $sex";
breno>           $q->{sex} = $sex;
breno>         }
   
breno>         @rows = $rs->search($q,$opts)->pager;
   
breno>         template 'results',
breno>           { query => $query,
breno>             mummies => \@rows,
breno>             rows => 1000
breno>           };
breno>       };
   
breno>       true;
breno>     _______________________________________________
breno>     dancer-users mailing list
breno>     dancer-users at dancer.pm
breno>     http://lists.preshweb.co.uk/mailman/listinfo/dancer-users

breno> _______________________________________________
breno> dancer-users mailing list
breno> dancer-users at dancer.pm
breno> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users