Module: ArJdbc::MsSQL::SqlServerLimitOffset

Defined in:

lib/arjdbc/mssql/adapter.rb

Instance Method Summary

• #add_limit_offset!(sql, options) ⇒ Object

Instance Method Details

#add_limit_offset!(sql, options) ⇒ Object

# File 'lib/arjdbc/mssql/adapter.rb', line 269

def add_limit_offset!(sql, options)
  limit = options[:limit]
  if limit
    offset = (options[:offset] || 0).to_i
    start_row = offset + 1
    end_row = offset + limit.to_i
    order = (options[:order] || determine_order_clause(sql))
    sql.sub!(/ ORDER BY.*$/i, '')
    find_select = /\b(SELECT(?:\s+DISTINCT)?)\b(.*)/im
    whole, select, rest_of_query = find_select.match(sql).to_a
    if rest_of_query.strip!.first == '*'
      from_table = /.*FROM\s*\b(\w*)\b/i.match(rest_of_query).to_a[1]
    end
    new_sql = "#{select} t.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY #{order}) AS _row_num, #{from_table + '.' if from_table}#{rest_of_query}"
    new_sql << ") AS t WHERE t._row_num BETWEEN #{start_row.to_s} AND #{end_row.to_s}"
    sql.replace(new_sql)
  end
end