| Module | Sequel::EmulateOffsetWithRowNumber |
| In: |
lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
|
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.
# 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.
# 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