Please check the gist, there's errors in my reply. On Wed, Oct 15, 2014 at 5:25 PM, Jacques Lareau <[email protected]> wrote:
> 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 <[email protected]> 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/QuickStart.pod >> and >> https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Intro.pod >> . >> >> 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 <[email protected]> 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 >>> [email protected] >>> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users >>> >> >> >> _______________________________________________ >> dancer-users mailing list >> [email protected] >> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users >> >> >
_______________________________________________ dancer-users mailing list [email protected] http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
