Module | Sequel::EmulateOffsetWithRowNumber
In: | lib/sequel/adapters/utils/emulate_offset_with_row_number.rb

### Methods

complex_expression_sql_append select_sql

### Public Instance methods

complex_expression_sql_append(sql, op, args)

When a subselect that uses :offset is used in IN or NOT IN, use a nested subselect that only includes the first column instead of the ROW_NUMBER column added by the emulated offset support.

[Source]

        # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 6
     6:     def complex_expression_sql_append(sql, op, args)
     7:       case op
     8:       when :IN, "NOT IN""NOT IN"
     9:         ds = args.at(1)
    10:         if ds.is_a?(Sequel::Dataset) && ds.opts[:offset]
    11:           c = ds.opts[:select].first
    12:           case c
    13:           when Symbol
    14:             t, cl, a = split_symbol(c)
    15:             if a
    16:               c = SQL::Identifier.new(a)
    17:             elsif t
    18:               c = SQL::Identifier.new(cl)
    19:             end
    20:           when SQL::AliasedExpression
    21:             c = SQL::Identifier.new(c.aliaz)
    22:           when SQL::QualifiedIdentifier
    23:             c = SQL::Identifier.new(c.column)
    24:           end
    25:           super(sql, op, [args.at(0), ds.from_self.select(c)])
    26:         else
    27:           super
    28:         end
    29:       else
    30:         super
    31:       end
    32:     end

select_sql()

Emulate OFFSET support with the ROW_NUMBER window function

The implementation is ugly, cloning the current dataset and modifying the clone to add a ROW_NUMBER window function (and some other things), then using the modified clone in a subselect which is selected from.

If offset is used, an order must be provided, because the use of ROW_NUMBER requires an order.

[Source]

        # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 42
    42:     def select_sql
    43:       return super unless o = @opts[:offset]
    44:
    45:       order = @opts[:order] || default_offset_order
    46:       if order.nil? || order.empty?
    47:         raise(Error, "#{db.database_type} requires an order be provided if using an offset")
    48:       end
    49:
    50:       dsa1 = dataset_alias(1)
    51:       rn = row_number_column
    52:       sql = @opts[:append_sql] || ''
    53:       subselect_sql_append(sql, unlimited.
    54:         unordered.
    55:         select_append{ROW_NUMBER(:over, :order=>order){}.as(rn)}.
    56:         from_self(:alias=>dsa1).
    57:         limit(@opts[:limit]).
    58:         where(SQL::Identifier.new(rn) > o).
    59:         order(rn))
    60:       sql
    61:     end

[Validate]