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

Methods

Public Instance methods

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

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]