Module | Sequel::EmulateOffsetWithRowNumber |
In: |
lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
|
If the offset must be emulated with ROW_NUMBER, don‘t remove any ordering, because it can cause invalid queries to be issued if an offset is required when ordering.
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 6 6: def empty? 7: return super unless emulate_offset_with_row_number? 8: get(Sequel::SQL::AliasedExpression.new(1, :one)).nil? 9: 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 19 19: def select_sql 20: return super unless emulate_offset_with_row_number? 21: 22: offset = @opts[:offset] 23: order = @opts[:order] 24: if require_offset_order? 25: order ||= default_offset_order 26: if order.nil? || order.empty? 27: raise(Error, "#{db.database_type} requires an order be provided if using an offset") 28: end 29: end 30: 31: columns = clone(:append_sql=>'', :placeholder_literal_null=>true).columns 32: dsa1 = dataset_alias(1) 33: rn = row_number_column 34: sql = @opts[:append_sql] || '' 35: subselect_sql_append(sql, unlimited. 36: unordered. 37: select_append{ROW_NUMBER{}.over(:order=>order).as(rn)}. 38: from_self(:alias=>dsa1). 39: select(*columns). 40: limit(@opts[:limit]). 41: where(SQL::Identifier.new(rn) > offset). 42: order(rn)) 43: sql 44: end