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 



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 67

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] == "1" && rest_of_query !~ /1 AS/i
      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
    # We might want to add the condition that rest_of_query has at least one JOIN outside of signle quotes
    if select =~ /DISTINCT/
      t_cols = rest_of_query.sub(/ FROM .*/, '').gsub(/\[?[A-Za-z0-9_]+\]?\./, 't.')
      new_sql = "SELECT #{t_cols} FROM (
                     SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS _row_num, #{t_cols} FROM (
                         SELECT DISTINCT #{t_cols} FROM (
                             SELECT ROW_NUMBER() OVER(#{order}) AS _row_num, #{rest_of_query}
                         ) AS t
                     ) AS t
                 ) AS t WHERE t._row_num BETWEEN #{start_row.to_s} AND #{end_row.to_s}"
    else
      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}"
    end
    sql.replace(new_sql)
  end
  sql
end