Module: ArJdbc::MsSQL::LimitHelpers::SqlServerReplaceLimitOffset

Included in:
Arel::Visitors::SQLServer
Defined in:
lib/arjdbc/mssql/limit_helpers.rb

Class Method Summary collapse

Class Method Details

.replace_limit_offset!(sql, limit, offset, order) ⇒ Object



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 64

def replace_limit_offset!(sql, limit, offset, order)
  if limit
    offset ||= 0
    start_row = offset + 1
    end_row = offset + limit.to_i
    find_select = /\b(SELECT(?:\s+DISTINCT)?)\b(.*)/im
    whole, select, rest_of_query = find_select.match(sql).to_a
    rest_of_query.strip!
    if rest_of_query[0] == "1"
      rest_of_query[0] = "*"
    end
    if rest_of_query[0] == "*"
      from_table = LimitHelpers.get_table_name(rest_of_query)
      rest_of_query = from_table + '.' + rest_of_query
    end
    new_sql = "#{select} t.* FROM (SELECT ROW_NUMBER() OVER(#{order}) AS _row_num, #{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
  sql
end