module Sequel::EmulateOffsetWithRowNumber

Public Instance Methods

empty?() click to toggle source

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.

Calls superclass method
# 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
select_sql() click to toggle source

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.

Calls superclass method
# 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
supports_offsets_in_correlated_subqueries?() click to toggle source

This does not support offsets in correlated subqueries, as it requires a query to get the columns that will be invalid if a correlated subquery is used.

# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 48
def supports_offsets_in_correlated_subqueries?
  false
end

Private Instance Methods

default_offset_order() click to toggle source

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
emulate_offset_with_row_number?() click to toggle source

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
require_offset_order?() click to toggle source

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