module Sequel::EmulateOffsetWithRowNumber

  1. lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
Parent: Sequel

Methods

Public Instance

  1. select_sql

Public Instance methods

select_sql ()

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.

[show source] 
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb, line 11
def select_sql
  return super unless o = @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=>'').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) > o).
    order(rn))
  sql
end