DBIx::Class::SQLMaker::LimitDialects(3) User Contributed Perl Documentation DBIx::Class::SQLMaker::LimitDialects(3)

DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker

This module replicates a lot of the functionality originally found in SQL::Abstract::Limit. While simple limits would work as-is, the more complex dialects that require e.g. subqueries could not be reliably implemented without taking full advantage of the metadata locked within DBIx::Class::ResultSource classes. After reimplementation of close to 80% of the SQL::Abstract::Limit functionality it was deemed more practical to simply make an independent DBIx::Class-specific limit-dialect provider.

Note that the actual implementations listed below never use "*" literally. Instead proper re-aliasing of selectors and order criteria is done, so that the limit dialect are safe to use on joined resultsets with clashing column names.

Currently the provided dialects are:

 SELECT ... LIMIT $limit OFFSET $offset

Supported by PostgreSQL and SQLite

 SELECT ... LIMIT $offset, $limit

Supported by MySQL and any SQL::Statement based DBD

 SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER( ORDER BY ... ) AS RNO__ROW__INDEX FROM (
   SELECT ...
  )
 ) WHERE RNO__ROW__INDEX BETWEEN ($offset+1) AND ($limit+$offset)

ANSI standard Limit/Offset implementation. Supported by DB2 and MSSQL >= 2005.

 SELECT SKIP $offset FIRST $limit * FROM ...

Supported by Informix, almost like LimitOffset. According to SQL::Abstract::Limit "... SKIP $offset LIMIT $limit ..." is also supported.

 SELECT FIRST $limit SKIP $offset * FROM ...

Supported by Firebird/Interbase, reverse of SkipFirst. According to SQL::Abstract::Limit "... ROWS $limit TO $offset ..." is also supported.

Depending on the resultset attributes one of:

 SELECT * FROM (
  SELECT *, ROWNUM AS rownum__index FROM (
   SELECT ...
  ) WHERE ROWNUM <= ($limit+$offset)
 ) WHERE rownum__index >= ($offset+1)

or

 SELECT * FROM (
  SELECT *, ROWNUM AS rownum__index FROM (
    SELECT ...
  )
 ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)

or

 SELECT * FROM (
    SELECT ...
  ) WHERE ROWNUM <= ($limit+1)

Supported by Oracle.

 SELECT * FROM
 SELECT TOP $limit FROM (
  SELECT TOP $limit FROM (
   SELECT TOP ($limit+$offset) ...
  ) ORDER BY $reversed_original_order
 ) ORDER BY $original_order

Unreliable Top-based implementation, supported by MSSQL < 2005.

CAVEAT

Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.

 SELECT * FROM
 (
 SELECT * FROM (
  SELECT * FROM (
   SELECT * FROM ...
  ) ORDER BY $reversed_original_order
    FETCH FIRST $limit ROWS ONLY
 ) ORDER BY $original_order
   FETCH FIRST $limit ROWS ONLY
 )

Unreliable FetchFirst-based implementation, supported by IBM DB2 <= V5R3.

CAVEAT

Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.

 SELECT * FROM (
  SELECT ...
 )
 WHERE (
  SELECT COUNT(*) FROM $original_table cnt WHERE cnt.id < $original_table.id
 ) BETWEEN $offset AND ($offset+$rows-1)

This is the most evil limit "dialect" (more of a hack) for really stupid databases. It works by ordering the set by some unique column, and calculating the amount of rows that have a less-er value (thus emulating a "RowNum"-like index). Of course this implies the set can only be ordered by a single unique column.

Also note that this technique can be and often is excruciatingly slow. You may have much better luck using "software_limit" in DBIx::Class::ResultSet instead.

Currently used by Sybase ASE, due to lack of any other option.

Check the list of additional DBIC resources.

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.

2018-01-29 perl v5.34.0