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

Methods

Public Instance methods

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 11
11:     def select_sql
12:       return super unless o = @opts[:offset]
13: 
14:       order = @opts[:order] || default_offset_order
15:       if order.nil? || order.empty?
16:         raise(Error, "#{db.database_type} requires an order be provided if using an offset")
17:       end
18: 
19:       columns = clone(:append_sql=>'').columns
20:       dsa1 = dataset_alias(1)
21:       rn = row_number_column
22:       sql = @opts[:append_sql] || ''
23:       subselect_sql_append(sql, unlimited.
24:         unordered.
25:         select_append{ROW_NUMBER(:over, :order=>order){}.as(rn)}.
26:         from_self(:alias=>dsa1).
27:         select(*columns).
28:         limit(@opts[:limit]).
29:         where(SQL::Identifier.new(rn) > o).
30:         order(rn))
31:       sql
32:     end

[Validate]