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