File Coverage

blib/lib/Teng/Plugin/SQLPager.pm
Criterion Covered Total %
statement 29 29 100.0
branch 4 6 66.6
condition 1 3 33.3
subroutine 5 5 100.0
pod 1 1 100.0
total 40 44 90.9


line stmt bran cond sub pod time code
1             package Teng::Plugin::SQLPager;
2 1     1   831 use strict;
  1         2  
  1         36  
3 1     1   6 use warnings;
  1         1  
  1         25  
4 1     1   5 use utf8;
  1         2  
  1         10  
5              
6             our @EXPORT = qw/search_by_sql_with_pager/;
7              
8 1     1   512 use Data::Page::NoTotalEntries;
  1         694  
  1         287  
9              
10             sub search_by_sql_with_pager {
11 2     2 1 4690 my ($self, $sql, $binds, $opt, $table_name) = @_;
12 2   33     32 $table_name ||= $self->_guess_table_name( $sql );
13              
14 2         3 my $page = 0+$opt->{page};
15 2         4 my $entries_per_page = 0+$opt->{rows};
16 2         6 my $offset = ( $page - 1 ) * $entries_per_page;
17              
18 2         4 $sql .= " LIMIT @{[ $entries_per_page + 1 ]} OFFSET $offset";
  2         9  
19              
20 2 50       11 my $sth = $self->dbh->prepare($sql) or Carp::croak $self->dbh->errstr;
21 2 50       433 $sth->execute(@$binds) or Carp::croak $self->dbh->errstr;
22              
23 2         14 my $itr = Teng::Iterator->new(
24             teng => $self,
25             sth => $sth,
26             sql => $sql,
27             row_class => $self->schema->get_row_class($table_name),
28             table_name => $table_name,
29             suppress_object_creation => $self->suppress_row_objects,
30             );
31 2         9 my $rows = [$itr->all];
32 2         4 my $has_next = 0;
33 2 100       6 if (@$rows == $entries_per_page + 1) {
34 1         2 pop @$rows;
35 1         5 $has_next++;
36             }
37              
38 2         9 my $pager = Data::Page::NoTotalEntries->new(
39             entries_per_page => $entries_per_page,
40             current_page => $page,
41             has_next => $has_next,
42             );
43              
44 2         47 return ($rows, $pager);
45             }
46              
47              
48             1;
49             __END__
50              
51             =head1 NAME
52              
53             Teng::Plugin::SQLPager - Paginate with SQL
54              
55             =head1 SYNOPSIS
56              
57             package My::DB;
58             use parent qw/Teng/;
59             __PACKAGE__->load_plugin(qw/SQLPager/);
60              
61             # in your application
62             $db->search_by_sql_with_pager(
63             q{SELECT * FROM member ORDER BY id DESC},
64             [],
65             {page => 1, rows => 20}
66             );
67              
68             =head1 DESCRIPTION
69              
70             This module searches database by SQL with paginate.
71              
72             search_by_sql_with_pager method adds LIMIT clause automatically.
73              
74             =head1 ARGUMENTS FOR search_by_sql_with_pager
75              
76             You can pass arguments as following.
77              
78             =head2 C<< $db->search_by_sql_with_pager($sql, $binds, $opt[, $table_name]); >>
79              
80             =over 4
81              
82             =item $sql: Str
83              
84             This is a SQL statement in string.
85              
86             =item $binds: ArrayRef[Str]
87              
88             This is a bind values in arrayref.
89              
90             =item $opt: HashRef
91              
92             Options for search_by_sql_with_pager. Important options are 'page' and 'rows'.
93              
94             B<page> is a current page number. B<rows> is a entries per page.
95              
96             =item $table_name: Str
97              
98             You can pass a table name.
99              
100             This argument is optional. If you don't pass a table name, Teng guess table name automatically.
101              
102             =back
103              
104             =head1 LIMITATIONS
105              
106             This module does not work with Oracle since Oracle does not support limit clause.
107