| Subject: | PATCH: add get_Pg_limit and get_Pg_sort |
Attached is a patch to add get_Pg_limit and get_Pg_sort.
For the future, I envision having
get_limit() and get_sort()
These would look at the DBI driver in use, and then try to dispatch to
one that works based on the name. I suspect the "get_mysql_sort()" will
prove to be highly compatible.
Both MySQL and PostgreSQL now support the "LIMIT x OFFSET y" syntax.
Mark
| Subject: | get_Pg_limit.diff |
Wed Jun 7 16:44:36 EDT 2006 mark@summersault.com
* Add get_Pg_sort and get_Pg_limit
diff -rN old-miami/perllib/HTML/ReportWriter/PagingAndSorting.pm new-miami/perllib/HTML/ReportWriter/PagingAndSorting.pm
323a324,329
> Without a function like C<FOUND_ROWS()>, this number could be generated by an additional SQL query that
> counts the total number of rows, using the same WHERE clause:
>
> my $count = $dbh->selectrow_array("SELECT count(*) FROM articles WHERE ...")
> $paging->num_results($count);
>
533a540,560
> =item B<get_Pg_limit()>
>
> $sql_limit_snippet = $self->get_Pg_limit();
>
> Generates a PostgreSQL-compliant LIMIT clause to be appended to SQL queries in order to get the
> appropriate rows for a paged report. Example above, in the SYNOPSIS.
>
> =cut
>
> # Named 'Pg' to match the driver spelling, allowing us to automatically choose
> # this based on the driver in the future.
> sub get_Pg_limit
> {
> my $self = shift;
>
> my $start = ($self->{'CURRENT_PAGE'} - 1) * $self->{'RESULTS_PER_PAGE'};
> my $count = $self->{'RESULTS_PER_PAGE'};
>
> return "LIMIT $count OFFSET $start";
> }
>
579a607,620
> =item B<get_Pg_sort()>
>
> $sql_order_by_snippet = $pager->get_Pg_sort();
>
> Returns a PostgreSQL-compliant ORDER BY clause based on the current sorting settings, to be appended
> to the SQL query used to generate the report that this module is being used for. Example above
> in the SYNOPSIS.
>
> =cut
>
> # The MySQL syntax is compatible with PostgreSQL
> *get_Pg_sort = \&get_mysql_sort;
>
>