Example of pagination with DBIC?
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;
And just to let people know, I've looked at: http://advent.perldancer.org/2012/20 http://www.slideshare.net/davorg/database-programming-with-perl-and-dbixclas... http://www.slideshare.net/ranguard/dbixclass-beginners-presentation http://www.perlmonks.org/?node=557402 http://search.cpan.org/~ribasushi/DBIx-Class-0.082801/lib/DBIx/Class/ResultS... http://www.dagolden.com/index.php/1807/my-second-week-of-dancer-now-with-que... and I'm just not getting all the details quite yet. Though it's probably me and not just quite able to bring it all together. Thanks, John
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
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 <garu@cpan.org> 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/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
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Please check the gist, there's errors in my reply. On Wed, Oct 15, 2014 at 5:25 PM, Jacques Lareau <jack.lareau@gmail.com> 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 <garu@cpan.org> 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/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
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Jacques> Here is how I do it. Check the gist for better formatting. Jacques> https://gist.github.com/jacqueslareau/1e9dcc04d416e1216161 Jacques> status_ok is because it's an API. You can use the value returned in a template instead. Thanks a ton for this pointer, I'll look into it more deeply. Now for a stupid question, has everyone moved away from using 'post' to instead using 'get' as the method of choice for form handling in general? I'm still stuck in the old PHP days honestly, but trying to move upto newer stuff. Tried Rails years ago, but all the examples of building a blog weren't what I needed/wanted. Sigh... I don't know why everyone things a blog is a good example. Anyway... sorry for whining. I'll take a look at your code and see how I can make it work for me. John
No. It's just more RESTful. I mainly build API with Dancer, so I tend to follow a RESTful design. http://en.wikipedia.org/wiki/Representational_state_transfer GET is used to list/query a collection and also retreive an object POST is used to create an object into a collection. PUT to update an object DELETE to delete an object Since you are quering a collection, I'd suggest using GET. On Thu, Oct 16, 2014 at 3:23 PM, John Stoffel <john@stoffel.org> wrote:
Jacques> Here is how I do it. Check the gist for better formatting. Jacques> https://gist.github.com/jacqueslareau/1e9dcc04d416e1216161
Jacques> status_ok is because it's an API. You can use the value returned in a template instead.
Thanks a ton for this pointer, I'll look into it more deeply.
Now for a stupid question, has everyone moved away from using 'post' to instead using 'get' as the method of choice for form handling in general? I'm still stuck in the old PHP days honestly, but trying to move upto newer stuff. Tried Rails years ago, but all the examples of building a blog weren't what I needed/wanted. Sigh... I don't know why everyone things a blog is a good example.
Anyway... sorry for whining. I'll take a look at your code and see how I can make it work for me.
John _______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Jacques> No. It's just more RESTful. I mainly build API with Dancer, Jacques> so I tend to follow a RESTful design. I'm familiar with RESTful ideas, just haven't made it work for me yet, but I'm not a programmer by trade and this is my side hobby at points. So I really do appreciate all your help here. Jacques> GET is used to list/query a collection and also retreive an object Jacques> POST is used to create an object into a collection. Jacques> PUT to update an object Jacques> DELETE to delete an object Yup, makes sense. Jacques> Since you are quering a collection, I'd suggest using GET. Ok, so how would you save the query parameters so that each time you page through the results, they get submitted cleanly? I'm looking at various simple pagination libs now to take the next step here. And looking at the 'bootstrap' framework to make it look pretty. I've got bigger problems down the line, since I will need to replace a big date library and calendar generation library written in PHP if I want to move forward for one existing DB I offer searches for. But that's not hear nor there. Let me get the basics working better first.
Since a GET will put parameters into the URL, you can just re-use the URL and change the page parameter. /?name=test&city=montreal&page=1 <- to change pages On Thu, Oct 16, 2014 at 4:55 PM, John Stoffel <john@stoffel.org> wrote:
Jacques> No. It's just more RESTful. I mainly build API with Dancer, Jacques> so I tend to follow a RESTful design.
I'm familiar with RESTful ideas, just haven't made it work for me yet, but I'm not a programmer by trade and this is my side hobby at points. So I really do appreciate all your help here.
Jacques> GET is used to list/query a collection and also retreive an object Jacques> POST is used to create an object into a collection. Jacques> PUT to update an object Jacques> DELETE to delete an object
Yup, makes sense.
Jacques> Since you are quering a collection, I'd suggest using GET.
Ok, so how would you save the query parameters so that each time you page through the results, they get submitted cleanly? I'm looking at various simple pagination libs now to take the next step here. And looking at the 'bootstrap' framework to make it look pretty.
I've got bigger problems down the line, since I will need to replace a big date library and calendar generation library written in PHP if I want to move forward for one existing DB I offer searches for. But that's not hear nor there. Let me get the basics working better first.
_______________________________________________ dancer-users mailing list dancer-users@dancer.pm http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
Jacques> Since a GET will put parameters into the URL, you can just Jacques> re-use the URL and change the page parameter. Jacques> /?name=test&city=montreal&page=1 <- to change pages So, I've been slowly hacking away at this and I think I have it mostly working, but the question I have now is what is the best way to build/modify the URL to support paging? For example, I use the following URL http://localhost:3000/?Name=&Sex=F&submit=Search when I do my simple query. My base page is 1 if I have multiple results. And after a bit of reading, I figure I can just use the request->request_uri helper to get when I need and strip out more stuff. Something like this, assuming I have more than 10 (default) results to show, etc: my $page_prev = $page - 1; my $page_next = $page + 1; my $rec_start = (($page-1) * $limit) + 1; my $rec_end = ($page * $limit) - 1; my $page_prev_uri = ""; my $page_next_uri = ""; if ($page_prev > 1) { my $uri = request->request_uri; $uri =~ s/\&page=\d+//; $page_prev_uri = join("",$uri, '&page=' . $page-1); } if ($page_next < POSIX::ceil($count / $limit)) { my $uri = request->request_uri; $uri =~ s/\&page=\d+//; $page_next_uri = join("",$uri,'&page=',$page+1); } All this is just basic math and works fine. The trick seems to be in the URI re-writing, which *almost* works. So my search form just has: <FORM ACTION="/" METHOD="get"> 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> <INPUT TYPE="submit" NAME="submit" VALUE="Search" /> </form> And the URI when submitted looks like: /?Name=&Sex=F&submit=Search So my questions are: 1. Should I strip off the "&submit=Search" part when I build my <A HREF=...>prev</A> and next links? 2. Should I pre-place a <INPUT type="hidden" name="page" value="1" /> field in my search form, with the obvious updating if I'm searching on a different page? 3. Right now I'm using a single template to both show the search form and the results. I'm thinking I really want to split this up... and of course I want to allow the users to "refine" a search so I need to pass back defaults for the various forms. Time to learn how Dancer does sessions... :-) Thanks, John
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@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@dancer.pm breno> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users breno> _______________________________________________ breno> dancer-users mailing list breno> dancer-users@dancer.pm breno> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
participants (3)
-
breno -
Jacques Lareau -
John Stoffel