module Sequel::EmulateOffsetWithRowNumber
Public Instance Methods
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 def empty? return super unless emulate_offset_with_row_number? get(Sequel::SQL::AliasedExpression.new(1, :one)).nil? 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 def select_sql return super unless emulate_offset_with_row_number? offset = @opts[:offset] order = @opts[:order] if require_offset_order? order ||= default_offset_order if order.nil? || order.empty? raise(Error, "#{db.database_type} requires an order be provided if using an offset") end end columns = clone(:append_sql=>'', :placeholder_literal_null=>true).columns 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). select(*columns). limit(@opts[:limit]). where(SQL::Identifier.new(rn) > offset). order(rn)) sql end
Private Instance Methods
The default order to use for datasets with offsets, if no order is defined. By default, orders by all of the columns in the dataset.
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 56 def default_offset_order clone(:append_sql=>'').columns end
Whether to use ROW_NUMBER to emulate offsets
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 66 def emulate_offset_with_row_number? @opts[:offset] && !@opts[:sql] end
Whether an order is required when using offset emulation via ROW_NUMBER, true by default.
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 61 def require_offset_order? true end