14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 14
def replace_limit_offset!(sql, limit, offset, order)
offset ||= 0
if match = FIND_SELECT.match(sql)
select, distinct, rest_of_query = match[1], match[2], match[3]
rest_of_query.strip!
end
rest_of_query[0] = '*' if rest_of_query[0...1] == '1' && rest_of_query !~ /1 AS/i
if rest_of_query[0...1] == '*'
from_table = Utils.get_table_name(rest_of_query, true)
rest_of_query = "#{from_table}.#{rest_of_query}"
end
if rest_of_query.downcase.include?('group by')
order_start = order.strip[0, 8]; order_start.upcase!
if order_start == 'ORDER BY' && order.match(FIND_AGGREGATE_FUNCTION)
elsif order.count(',') == 0
order.gsub!(/ORDER +BY +([^\s]+)(\s+ASC|\s+DESC)?/i, 'ORDER BY MIN(\1)\2')
else
raise('Only one order condition allowed.')
end
end
if distinct order = order.gsub(/([a-z0-9_])+\./, 't.')
new_sql = "SELECT t.* FROM "
new_sql << "( SELECT ROW_NUMBER() OVER(#{order}) AS _row_num, t.* FROM (#{select} #{rest_of_query}) AS t ) AS t"
append_limit_row_num_clause(new_sql, limit, offset)
else
select_columns_before_from = rest_of_query.gsub(/FROM.*/, '').strip
only_one_column = !select_columns_before_from.include?(',')
only_one_id_column = only_one_column && (select_columns_before_from.ends_with?('.id') || select_columns_before_from.ends_with?('.[id]'))
if only_one_id_column
new_sql = "#{select} t.id FROM "
else
new_sql = "#{select} t.* FROM "
end
new_sql << "( SELECT ROW_NUMBER() OVER(#{order}) AS _row_num, #{rest_of_query} ) AS t"
append_limit_row_num_clause(new_sql, limit, offset)
end
sql.replace new_sql
end
|