Module: Sequel::EmulateOffsetWithRowNumber

Included in:
DB2::DatasetMethods, MSSQL::DatasetMethods, Oracle::DatasetMethods
Defined in:
lib/sequel/adapters/utils/emulate_offset_with_row_number.rb

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql_append(sql, op, args) ⇒ Object

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.



6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/sequel/adapters/utils/emulate_offset_with_row_number.rb', line 6

def complex_expression_sql_append(sql, op, args)
  case op
  when :IN, :"NOT IN"
    ds = args.at(1)
    if ds.is_a?(Sequel::Dataset) && ds.opts[:offset]
      c = ds.opts[:select].first
      case c
      when Symbol
        t, cl, a = split_symbol(c)
        if a
          c = SQL::Identifier.new(a)
        elsif t
          c = SQL::Identifier.new(cl)
        end
      when SQL::AliasedExpression
        c = SQL::Identifier.new(c.aliaz)
      when SQL::QualifiedIdentifier
        c = SQL::Identifier.new(c.column)
      end
      super(sql, op, [args.at(0), ds.from_self.select(c)])
    else
      super
    end
  else
    super
  end
end

#select_sql ⇒ Object

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.

Raises:



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
# File 'lib/sequel/adapters/utils/emulate_offset_with_row_number.rb', line 42

def select_sql
  return super unless o = @opts[:offset]
  raise(Error, "#{db.database_type} requires an order be provided if using an offset") unless order = @opts[:order]
  dsa1 = dataset_alias(1)
  rn = row_number_column
  sql = @opts[:append_sql] || ''
  subselect_sql_append(sql, unlimited.
    unordered.
    select_append{ROW_NUMBER(:over, :order=>order){}.as(rn)}.
    from_self(:alias=>dsa1).
    limit(@opts[:limit]).
    where(SQL::Identifier.new(rn) > o).
    order(rn))
  sql
end