module ArJdbc::MSSQL::LimitHelpers::SqlServerReplaceLimitOffset

@private

Constants

GROUP_BY
ORDER_BY

Public Instance Methods

append_limit_row_num_clause(sql, limit, offset) click to toggle source
# File lib/arjdbc/mssql/limit_helpers.rb, line 77
def append_limit_row_num_clause(sql, limit, offset)
  if limit
    start_row = offset + 1; end_row = offset + limit.to_i
    sql << " WHERE t._row_num BETWEEN #{start_row} AND #{end_row}"
  else
    sql << " WHERE t._row_num > #{offset}"
  end
end
replace_limit_offset!(sql, limit, offset, order) click to toggle source
# File lib/arjdbc/mssql/limit_helpers.rb, line 18
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

  # Ensure correct queries if the rest_of_query contains a 'GROUP BY'. Otherwise the following error occurs:
  #   ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: Column 'users.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  #   SELECT t.* FROM ( SELECT ROW_NUMBER() OVER(ORDER BY users.id) AS _row_num, [users].[lft], COUNT([users].[lft]) FROM [users] GROUP BY [users].[lft] HAVING COUNT([users].[lft]) > 1 ) AS t WHERE t._row_num BETWEEN 1 AND 1
  if i = ( rest_of_query.rindex(GROUP_BY) || rest_of_query.rindex('group by') )
    # Do not catch 'GROUP BY' statements from sub-selects, indicated
    # by more closing than opening brackets after the last group by.
    rest_after_last_group_by = rest_of_query[i..-1]
    opening_brackets_count = rest_after_last_group_by.count('(')
    closing_brackets_count = rest_after_last_group_by.count(')')

    if opening_brackets_count == closing_brackets_count
      order_start = order.strip[0, 8]; order_start.upcase!
      if order_start == ORDER_BY && order.match(FIND_AGGREGATE_FUNCTION)
        # do nothing
      elsif order.count(',') == 0
        order.gsub!(/ORDER +BY +([^\s]+)(\s+ASC|\s+DESC)?/i, 'ORDER BY MIN(\1)\2')
      else
        raise("can not handle multiple order conditions (#{order.inspect}) in #{sql.inspect}")
      end
    end
  end

  if distinct # select =~ /DISTINCT/i
    order = order.gsub(/(\[[a-z0-9_]+\]|[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
      # If there's only one id column a subquery will be created which only contains this column
      new_sql = "#{select} t.id FROM "
    else
      # All selected columns are used
      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