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 8 def empty? 9 return super unless emulate_offset_with_row_number? 10 select(Dataset::EMPTY_SELECT).limit(1).single_value!.nil? 11 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 21 def select_sql 22 return super unless emulate_offset_with_row_number? 23 24 offset = @opts[:offset] 25 order = @opts[:order] 26 if require_offset_order? 27 order ||= default_offset_order 28 if order.nil? || order.empty? 29 raise(Error, "#{db.database_type} requires an order be provided if using an offset") 30 end 31 end 32 33 columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns 34 dsa1 = dataset_alias(1) 35 rn = row_number_column 36 sql = @opts[:append_sql] || String.new 37 subselect_sql_append(sql, unlimited. 38 unordered. 39 select_append(Sequel.function(:ROW_NUMBER).over(:order=>order).as(rn)). 40 from_self(:alias=>dsa1). 41 select(*columns). 42 limit(@opts[:limit]). 43 where(SQL::Identifier.new(rn) > offset). 44 order(rn)) 45 sql 46 end
Private Instance Methods
Allow preparing prepared statements, since determining the prepared sql to use for a prepared statement requires calling prepare on that statement.
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb 58 def allow_preparing_prepared_statements? 59 true 60 end
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 64 def default_offset_order 65 if (cols = opts[:select]) 66 cols.each do |c| 67 case c 68 when Symbol 69 return [split_alias(c).first] 70 when SQL::Identifier, SQL::QualifiedIdentifier 71 return [c] 72 when SQL::AliasedExpression 73 case c.expression 74 when Symbol, SQL::Identifier, SQL::QualifiedIdentifier 75 return [c.expression] 76 end 77 end 78 end 79 end 80 clone(:append_sql=>String.new).columns 81 end
Whether to use ROW_NUMBER to emulate offsets
# File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb 89 def emulate_offset_with_row_number? 90 @opts[:offset] && !@opts[:sql] 91 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 84 def require_offset_order? 85 true 86 end