DBIx::C...ialects(3)

💖 NAME 💖

Okay bestie, welcome to the DBIx::C...ialects(3) page — yes, it’s User Contributed Perl Documentation, and it’s literally all about DBIx::Class::SQLMaker::LimitDialects: the SQL::Abstract::Limit-like functionality living inside DBIx::Class::SQLMaker. Slay. ✨

🦋 DESCRIPTION 🦋

So DBIC’s SQLMaker stack replicates and surpasses all of the functionality originally found in SQL::Abstract::Limit. Simple limits? They’d work as-is. But 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. Periodt. 💅

🌈 SQL LIMIT DIALECTS 🌈

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

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 ...

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

🎀 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. Cute. 🌸

👑 RowNum

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. Iconic behavior ngl. 🧡

🔥 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.

Not me warning you with love: this one is lowkey sketchy. 😭

🦋 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.

Again: incorrect results when $limit+$offset > total amount of rows in the resultset. Bestie, be careful. 🙈

🧚 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. It’s giving “last resort” energy fr fr. 💀

❓ FURTHER QUESTIONS? ❓

Check the list of additional DBIC resources. 💖

📜 COPYRIGHT AND LICENSE 📜

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.

---

That’s the whole page, bestie. Source/version: perl v5.42.0 · Date: 2025-01-22 · Manual: DBIx::C...ialects(3) 💅✨