<div dir="ltr"><div>Here is how I do it. Check the gist for better formatting.<br><br><a href="https://gist.github.com/jacqueslareau/1e9dcc04d416e1216161">https://gist.github.com/jacqueslareau/1e9dcc04d416e1216161</a><br><br></div>status_ok is because it's an API. You can use the value returned in a template instead.<br><br>------------------------<br><br>get '/' => sub {<br><br> my $rows = params->{rows} // 10;<br> my $page = params->{page} // 1;<br> my $keyword = params->{keyword};<br> my $sort_direction = 'desc';<br> my $sort_by = 'id';<br> <br> # make sure to validate parameters<br> <br> # build your where from your parameters<br> my %where;<br> $where{description} = { -like => "%$keyword%" } if defined $keyword;<br><br> my $things = rset('Project')->search(\%where, { <br> page => $page, <br> rows => $rows, <br> order_by => { '-desc' . $sort_direction => $sort_by },<br> });<br> <br> my @results;<br> while ( my $thing = $things->next ) {<br> push @results, {<br> id => $thing->id,<br> description => $thing->description<br> };<br> }<br> <br> status_ok({ <br> results => \@results,<br> pagination => { <br> total => $projects->pager->total_entries, <br> rows => $rows,<br> page => $page <br> }<br> });<br><br>}<br><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Oct 15, 2014 at 5:14 PM, breno <span dir="ltr"><<a href="mailto:garu@cpan.org" target="_blank">garu@cpan.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div><div><div><div><div>Hey John!<br><br></div>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.<br><br>Going back to your question: in terms of paging with Dancer and Dancer::Plugin::DBIC, this is what you could do:<br><br>----------------8<-----------------<br></div>get '/search' => sub {<br></div><div> # what the user is searching: (e.g.: /search?q=whatever)<br></div> my $query = param('q');<br><br></div><div> # which page to render (e.g.: /search?q=whatever&page=2)<br></div> my $page = param('page');<br><br></div><div> # make sure it's a valid page:<br></div><div> $page = 1 unless $page && $page =~ /^\d+$/;<br></div><div><br></div><div> # create your search parameters (I'm gonna handwave this one)<br></div><div> my %search_params = ( ... );<br></div><div><br></div><div> # fetch the proper page on the database<br></div> my @results = rset('MyRS')->search(<br> \%search_params,<br> {<br></div> rows => 10, # <=== how many items per page<br> }<br></div> )->page( $page )->all;<br><br></div> template 'mytemplatename', { search_results => \@results };<br>};<br>---------------->8-----------------<br><br></div><div><div>Does this help you at all? I would also read through <a href="https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/QuickStart.pod" target="_blank">https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/QuickStart.pod</a> and <a href="https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Intro.pod" target="_blank">https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Intro.pod</a>.<br><br></div><div>And of course, if you have any other questions, feel free to ask :)<br><br></div><div>Cheers!<br><br></div><div>garu<br></div><div><br><br></div></div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Oct 15, 2014 at 5:29 PM, John Stoffel <span dir="ltr"><<a href="mailto:john@stoffel.org" target="_blank">john@stoffel.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
Hi all,<br>
<br>
My google-fu is weak and I haven't been able to find a good example of<br>
how to do pagination with Dancer, DBIC and ideally some basic<br>
Javascript to make it look good.<br>
<br>
Here's my basic code that I'm working with right now, which I know is<br>
semi busted, but I just keep hitting roadblocks. The goal here is to<br>
let me spin up quick, but nice search forms for read only mysql DBs<br>
for a library I work with in my spare time. Ideally a CRUD interface<br>
down the line to allow end users to add/edit records. DBIC looks to<br>
be the way to go with this down the line, since it can do proper<br>
records and transactions. At least I think it can.<br>
<br>
Any way, my code:<br>
<br>
package TestDBIC;<br>
use Dancer ':syntax';<br>
use Dancer::Plugin::DBIC qw(schema resultset rset);<br>
<br>
# cd TestDBIC;<br>
# TestDBIC::Schema 'dbi:mysql:dbname=emina;host=quad' emina<br>
<br>
our $VERSION = '0.1';<br>
<br>
get '/' => sub {<br>
template 'search';<br>
};<br>
<br>
post '/results' => sub {<br>
my $name = param 'Name';<br>
my $sex = param 'Sex';<br>
my $city = param 'City';<br>
my $state = param 'State';<br>
my $id = param 'MummyID';<br>
<br>
my @rows;<br>
my $query;<br>
my $q = {};<br>
<br>
my $limit = 10;<br>
my $opts = {};<br>
$opts->{order_by} = { -desc => 'id' };<br>
$opts->{rows} = $limit if defined $limit;<br>
# We need to join in several tables from the schema<br>
$opts->{page} = 1;<br>
$opts->{limit} = 15;<br>
$opts->{order_by} = { 'desc' => 'name'};<br>
<br>
my $rs = schema->resultset('MummyInNorthAmerica');<br>
<br>
if ($name) {<br>
if ($query) { $query .= " AND "; }<br>
$query .= "Name LIKE $name";<br>
$q->{name} = { like => "%$name%" } ;<br>
}<br>
<br>
if ($sex) {<br>
if ($query) { $query .= " AND "; }<br>
$query .= "Sex = $sex";<br>
$q->{sex} = $sex;<br>
}<br>
<br>
@rows = $rs->search($q,$opts)->pager;<br>
<br>
template 'results',<br>
{ query => $query,<br>
mummies => \@rows,<br>
rows => 1000<br>
};<br>
};<br>
<br>
true;<br>
_______________________________________________<br>
dancer-users mailing list<br>
<a href="mailto:dancer-users@dancer.pm" target="_blank">dancer-users@dancer.pm</a><br>
<a href="http://lists.preshweb.co.uk/mailman/listinfo/dancer-users" target="_blank">http://lists.preshweb.co.uk/mailman/listinfo/dancer-users</a><br>
</blockquote></div><br></div>
</div></div><br>_______________________________________________<br>
dancer-users mailing list<br>
<a href="mailto:dancer-users@dancer.pm">dancer-users@dancer.pm</a><br>
<a href="http://lists.preshweb.co.uk/mailman/listinfo/dancer-users" target="_blank">http://lists.preshweb.co.uk/mailman/listinfo/dancer-users</a><br>
<br></blockquote></div><br></div>