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:       raise(Error, "#{db.database_type} requires an order be provided if using an offset") unless order = @opts[:order]
45:       dsa1 = dataset_alias(1)
46:       rn = row_number_column
47:       sql = @opts[:append_sql] || ''
48:       subselect_sql_append(sql, unlimited.
49:         unordered.
50:         select_append{ROW_NUMBER(:over, :order=>order){}.as(rn)}.
51:         from_self(:alias=>dsa1).
52:         limit(@opts[:limit]).
53:         where(SQL::Identifier.new(rn) > o).
54:         order(rn))
55:       sql
56:     end

[Validate]