Module: ArJdbc::MsSQL::SqlServer2000LimitOffset

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 232

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 (start_row == 1) && (end_row ==1)
      new_sql = "#{select} TOP 1 #{rest_of_query}"
      sql.replace(new_sql)
    else
      #UGLY
      #KLUDGY?
      #removing out stuff before the FROM...
      rest = rest_of_query[/FROM/i=~ rest_of_query.. -1]
      #need the table name for avoiding amiguity
      table_name = get_table_name(sql)
      #I am not sure this will cover all bases.  but all the tests pass
      new_order = "#{order}, #{table_name}.id" if order.index("#{table_name}.id").nil?
      new_order ||= order

      if (rest_of_query.match(/WHERE/).nil?)
        new_sql = "#{select} TOP #{limit} #{rest_of_query} WHERE #{table_name}.id NOT IN (#{select} TOP #{offset} #{table_name}.id #{rest} ORDER BY #{new_order}) ORDER BY #{order} "
      else
        new_sql = "#{select} TOP #{limit} #{rest_of_query} AND #{table_name}.id NOT IN (#{select} TOP #{offset} #{table_name}.id #{rest} ORDER BY #{new_order}) ORDER BY #{order} "
      end

      sql.replace(new_sql)
    end
  end
end