1SQL::Abstract::Limit(3)User Contributed Perl DocumentatioSnQL::Abstract::Limit(3)
2
6 SQL::Abstract::Limit - portable LIMIT emulation
7
9 use SQL::Abstract::Limit;
10 my $sql = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );;
12 # or autodetect from a DBI $dbh:
14 my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
15 # or from a Class::DBI class:
17 my $sql = SQL::Abstract::Limit->new( limit_dialect => 'My::CDBI::App' );
18 # or object:
20 my $obj = My::CDBI::App->retrieve( $id );
21 my $sql = SQL::Abstract::Limit->new( limit_dialect => $obj );
22 # generate SQL:
24 my ( $stmt, @bind ) = $sql->select( $table, \@fields, \%where, \@order, $limit, $offset );
25 # Then, use these in your DBI statements
27 my $sth = $dbh->prepare( $stmt );
28 $sth->execute( @bind );
29 # Just generate the WHERE clause (only available for some syntaxes)
31 my ( $stmt, @bind ) = $sql->where( \%where, \@order, $limit, $offset );
32
34 Portability layer for LIMIT emulation.
35 new( case => 'lower', cmp => 'like', logic => 'and', convert =>
37 'upper', limit_dialect => 'Top' )
38 All settings are optional.
39 limit_dialect
41 Sets the default syntax model to use for emulating a "LIMIT
42 $rows OFFSET $offset" clause. Default setting is
43 "GenericSubQ". You can still pass other syntax settings in
44 method calls, this just sets the default. Possible values
45 are:
46 LimitOffset PostgreSQL, SQLite
48 LimitXY MySQL, MaxDB, anything that uses SQL::Statement
49 LimitYX SQLite (optional)
50 RowsTo InterBase/FireBird
51 Top SQL/Server, MS Access
53 RowNum Oracle
54 FetchFirst DB2
55 Skip Informix
56 GenericSubQ Sybase, plus any databases not recognised by this module
57 $dbh a DBI database handle
59 CDBI subclass
61 CDBI object
62 other DBI-based thing
64 The first group are implemented by appending a short clause
66 to the end of the statement. The second group require more
67 intricate wrapping of the original statement in subselects.
68 You can pass a DBI database handle, and the module will
70 figure out which dialect to use.
71 You can pass a Class::DBI subclass or object, and the
73 module will find the $dbh and use it to find the dialect.
74 Anything else based on DBI can be easily added by locating
76 the $dbh. Patches or suggestions welcome.
77 Other options are described in SQL::Abstract.
79 select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [
81 $dialect ] ] ] )
82 Same as "SQL::Abstract::select", but accepts additional $rows,
83 $offset and $dialect parameters.
84 The $order parameter is required if $rows is specified.
86 The $fields parameter is required, but can be set to "undef", '' or
88 '*' (all these get set to '*').
89 The $where parameter is also required. It can be a hashref or an
91 arrayref, or "undef".
92 where( [ $where, [ \@order, [ $rows, [ $offset ], [ $dialect ] ] ] ] )
94 Same as "SQL::Abstract::where", but accepts additional $rows,
95 $offset and $dialect parameters.
96 Some SQL dialects support syntaxes that can be applied as simple
98 phrases tacked on to the end of the WHERE clause. These are:
99 LimitOffset
101 LimitXY
102 LimitYX
103 RowsTo
104 This method returns a modified WHERE clause, if the limit syntax is
106 set to one of these options (either in the call to "where" or in
107 the constructor), and if $rows is passed in.
108 Dies via "croak" if you try to use it for other syntaxes.
110 $order is required if $rows is set.
112 $where is required if any other parameters are specified. It can be
114 a hashref or an arrayref, or "undef".
115 Returns a regular "WHERE" clause if no limits are set.
117 insert
119 update
120 delete
121 values
122 generate
123 See SQL::Abstract for these methods.
124 "update" and "delete" are not provided with any "LIMIT" emulation
126 in this release, and no support is planned at the moment. But
127 patches would be welcome.
128 Limit emulation
130 The following dialects are available for emulating the LIMIT clause. In
131 each case, $sql represents the SQL statement generated by
132 "SQL::Abstract::select", minus the ORDER BY clause, e.g.
133 SELECT foo, bar FROM my_table WHERE some_conditions
135 $sql_after_select represents $sql with the leading "SELECT" keyword
137 removed.
138 "order_cols_up" represents the sort column(s) and direction(s)
140 specified in the "order" parameter.
141 "order_cols_down" represents the opposite sort.
143 "$last = $rows + $offset"
145 LimitOffset
147 Syntax
148 $sql ORDER BY order_cols_up LIMIT $rows OFFSET $offset
149 or
151 $sql ORDER BY order_cols_up LIMIT $rows
153 if "$offset == 0".
155 Databases
157 PostgreSQL
158 SQLite
159 LimitXY
161 Syntax
162 $sql ORDER BY order_cols_up LIMIT $offset, $rows
163 or
165 $sql ORDER BY order_cols_up LIMIT $rows
167 if "$offset == 0".
169 Databases
171 MySQL
172 LimitYX
174 Syntax
175 $sql ORDER BY order_cols_up LIMIT $rows, $offset
176 or
178 $sql ORDER BY order_cols_up LIMIT $rows
180 if "$offset == 0".
182 Databases
184 SQLite understands this syntax, or LimitOffset. If autodetecting the
185 dialect, it will be set to LimitOffset.
186 RowsTo
188 Syntax
189 $sql ORDER BY order_cols_up ROWS $offset TO $last
190 Databases
192 InterBase
193 FireBird
194 Top
196 Syntax
197 SELECT * FROM
198 (
199 SELECT TOP $rows * FROM
200 (
201 SELECT TOP $last $sql_after_select
202 ORDER BY order_cols_up
203 ) AS foo
204 ORDER BY order_cols_down
205 ) AS bar
206 ORDER BY order_cols_up
207 Databases
209 SQL/Server
210 MS Access
211 RowNum
213 Syntax Oracle numbers rows from 1, not zero, so here $offset has
214 been incremented by 1.
215 SELECT * FROM
217 (
218 SELECT A.*, ROWNUM r FROM
219 (
220 $sql ORDER BY order_cols_up
221 ) A
222 WHERE ROWNUM <= $last
223 ) B
224 WHERE r >= $offset
225 Databases
227 Oracle
228 FetchFirst
230 Syntax
231 SELECT * FROM (
232 SELECT * FROM (
233 $sql
234 ORDER BY order_cols_up
235 FETCH FIRST $last ROWS ONLY
236 ) foo
237 ORDER BY order_cols_down
238 FETCH FIRST $rows ROWS ONLY
239 ) bar
240 ORDER BY order_cols_up
241 Databases
243 IBM DB2
244 GenericSubQ
246 When all else fails, this should work for many databases, but it is
247 probably fairly slow.
248 This method relies on having a column with unique values as the
250 first column in the "SELECT" clause (i.e. the first column in the
251 "\@fields" parameter). The results will be sorted by that unique
252 column, so any $order parameter is ignored, unless it matches the
253 unique column, in which case the direction of the sort is honored.
254 Syntax
256 SELECT field_list FROM $table X WHERE where_clause AND
257 (
258 SELECT COUNT(*) FROM $table WHERE $pk > X.$pk
259 )
260 BETWEEN $offset AND $last
261 ORDER BY $pk $asc_desc
262 $pk is the first column in "field_list".
264 $asc_desc is the opposite direction to that specified in
266 the method call. So if you want the final results sorted
267 "ASC", say so, and it gets flipped internally, but the
268 results come out as you'd expect. I think.
269 The "BETWEEN $offset AND $last" clause is replaced with "<
271 $rows" if <$offset == 0>.
272 Databases
274 Sybase Anything not otherwise known to this module.
275 Skip
277 Syntax
278 select skip 5 limit 5 * from customer
279 which will take rows 6 through 10 in the select.
281 Databases
283 Informix
284
286 You can create your own syntax by making a subclass that provides an
287 "emulate_limit" method. This might be useful if you are using stored
288 procedures to provide more efficient paging.
289 emulate_limit( $self, $sql, $order, $rows, $offset )
291 $sql
292 This is the SQL statement built by SQL::Abstract, but without
293 the ORDER BY clause, e.g.
294 SELECT foo, bar FROM my_table WHERE conditions
296 or just
298 WHERE conditions
300 if calling "where" instead of "select".
302 $order
304 The "order" parameter passed to the "select" or "where" call.
305 You can get an "ORDER BY" clause from this by calling
306 my $order_by = $self->_order_by( $order );
308 You can get a pair of "ORDER BY" clauses that sort in opposite
310 directions by saying
311 my ( $up, $down ) = $self->_order_directions( $order );
313 The method should return a suitably modified SQL statement.
315
317 The $dialect parameter that can be passed to the constructor or to the
318 "select" and "where" methods can be a number of things. The module will
319 attempt to determine the appropriate syntax to use.
320 Supported $dialect things are:
322 dialect name (e.g. LimitOffset, RowsTo, Top etc.)
324 database moniker (e.g. Oracle, SQLite etc.)
325 DBI database handle
326 Class::DBI subclass or object
327
329 Paging results sets is a complicated undertaking, with several
330 competing factors to take into account. This module does not magically
331 give you the optimum paging solution for your situation. It gives you a
332 solution that may be good enough in many situations. But if your tables
333 are large, the SQL generated here will often not be efficient. Or if
334 your queries involve joins or other complications, you will probably
335 need to look elsewhere.
336 But if your tables aren't too huge, and your queries straightforward,
338 you can just plug this module in and move on to your next task.
339
341 Thanks to Aaron Johnson for the Top syntax model (SQL/Server and MS
342 Access).
343 Thanks to Emanuele Zeppieri for the IBM DB2 syntax model.
345 Thanks to Paul Falbe for the Informix implementation.
347
349 Find more syntaxes to implement.
350 Test the syntaxes against real databases. I only have access to MySQL.
352 Reports of success or failure would be great.
353
355 SQL::Abstract, DBI::Const::GetInfoType, Carp.
356
358 DBIx::SQLEngine, DBIx::SearchBuilder, DBIx::RecordSet.
359
361 Please report all bugs (patches welcome) via GitHub at
362 <https://github.com/asb-capfan/SQL-Abstract-Limit> or via the CPAN
363 Request Tracker at
364 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Abstract-Limit>.
365
367 Copyright 2004-2020 by David Baird. Currently maintained by Alexander
368 Becker.
369 This library is free software; you can redistribute it and/or modify it
371 under the same terms as Perl itself.
372
374 A few CPAN modules do this for a few databases, but the most
375 comprehensive seem to be DBIx::SQLEngine, DBIx::SearchBuilder and
376 DBIx::RecordSet.
377 Have a look in the source code for my notes on how these modules tackle
379 similar problems.
380perl v5.36.0 2023-01-20 SQL::Abstract::Limit(3)