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

Methods

Public Instance methods

If the offset must be emulated with ROW_NUMBER, don‘t remove any ordering, because it can cause invalid queries to be issued if an offset is required when ordering.

[Source]

   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 6
6:     def empty?
7:       return super unless emulate_offset_with_row_number?
8:       get(Sequel::SQL::AliasedExpression.new(1, :one)).nil?
9:     end

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 19
19:     def select_sql
20:       return super unless emulate_offset_with_row_number?
21: 
22:       offset = @opts[:offset]
23:       order = @opts[:order]
24:       if require_offset_order?
25:         order ||= default_offset_order
26:         if order.nil? || order.empty?
27:           raise(Error, "#{db.database_type} requires an order be provided if using an offset")
28:         end
29:       end
30: 
31:       columns = clone(:append_sql=>'', :placeholder_literal_null=>true).columns
32:       dsa1 = dataset_alias(1)
33:       rn = row_number_column
34:       sql = @opts[:append_sql] || ''
35:       subselect_sql_append(sql, unlimited.
36:         unordered.
37:         select_append{ROW_NUMBER{}.over(:order=>order).as(rn)}.
38:         from_self(:alias=>dsa1).
39:         select(*columns).
40:         limit(@opts[:limit]).
41:         where(SQL::Identifier.new(rn) > offset).
42:         order(rn))
43:       sql
44:     end

This does not support offsets in correlated subqueries, as it requires a query to get the columns that will be invalid if a correlated subquery is used.

[Source]

    # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 48
48:     def supports_offsets_in_correlated_subqueries?
49:       false
50:     end

[Validate]