|
|
How to: IFNULL(SELECT ...,0)
|
|
DBIx::Class::SQLMaker::LimitDialects(3) User Contributed Perl Documentation DBIx::Class::SQLMaker::LimitDialects(3) NAME
DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality for DBIx::Class::SQLMaker DESCRIPTION
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. SQL LIMIT DIALECTS
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: LimitOffset SELECT ... LIMIT $limit OFFSET $offset Supported by PostgreSQL and SQLite LimitXY SELECT ... LIMIT $offset $limit Supported by MySQL and any SQL::Statement based DBD RowNumberOver 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. SkipFirst SELECT SKIP $offset FIRST $limit * FROM ... Suported by Informix, almost like LimitOffset. According to SQL::Abstract::Limit "... SKIP $offset LIMIT $limit ..." is also supported. FirstSkip 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. RowNum Depending on the resultset attributes one of: SELECT * FROM ( SELECT *, ROWNUM rownum__index FROM ( SELECT ... ) WHERE ROWNUM <= ($limit+$offset) ) WHERE rownum__index >= ($offset+1) or SELECT * FROM ( SELECT *, ROWNUM rownum__index FROM ( SELECT ... ) ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset) or SELECT * FROM ( SELECT ... ) WHERE ROWNUM <= ($limit+1) Supported by Oracle. Top 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. FetchFirst 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. GenericSubQ 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. AUTHORS
See "CONTRIBUTORS" in DBIx::Class. LICENSE
You may distribute this code under the same terms as Perl itself. perl v5.18.2 2014-01-22 DBIx::Class::SQLMaker::LimitDialects(3)
