module TopNLoader::SQLBuilder

Public Class Methods

condition_sql(klass, condition) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 2
def self.condition_sql(klass, condition)
  condition_sql = where_condition_to_sql condition
  inheritance_column = klass.inheritance_column
  return condition_sql unless klass.has_attribute?(inheritance_column) && klass.base_class != klass
  sti_names = [klass, *klass.descendants].map(&:sti_name).compact
  sti_sql = where_condition_to_sql inheritance_column => sti_names
  [condition_sql, sti_sql].compact.join ' AND '
end
kv_condition_to_sql(key, value) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 120
def self.kv_condition_to_sql(key, value)
  return "NOT (#{where_condition_to_sql(value)})" if key == :not
  sql_binds = begin
    case value
    when NilClass
      %(#{q key} IS NULL)
    when Range
      if value.exclude_end?
        [%(#{q key} >= ? AND #{q key} < ?), value.begin, value.end]
      else
        [%(#{q key} BETWEEN ? AND ?), value.begin, value.end]
      end
    when Hash
      raise ArgumentError, '' unless value.keys == [:not]
      "NOT (#{kv_condition_to_sql(key, value[:not])})"
    when Enumerable
      array = value.to_a
      if array.include? nil
        [%((#{q key} IS NULL OR #{q key} IN (?))), array.reject(&:nil?)]
      else
        [%(#{q key} IN (?)), array]
      end
    else
      [%(#{q key} = ?), value]
    end
  end
  sanitize_sql_array sql_binds
end
q(name) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 79
def self.q(name)
  ActiveRecord::Base.connection.quote_column_name name
end
qt(name) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 83
def self.qt(name)
  ActiveRecord::Base.connection.quote_table_name name
end
sanitize_sql_array(array) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 149
def self.sanitize_sql_array(array)
  ActiveRecord::Base.send :sanitize_sql_array, array
end
top_n_association_sql(klass, target_klass, relation, limit:, order_mode:, order_key:) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 11
def self.top_n_association_sql(klass, target_klass, relation, limit:, order_mode:, order_key:)
  parent_table = klass.table_name
  joins = klass.joins relation.to_sym
  target_table = target_klass.table_name
  if target_table == klass.table_name
    target_table = "#{joins.joins_values.first.to_s.pluralize}_#{target_table}"
  end
  join_sql = joins.to_sql.match(/FROM.+/)[0]
  %(
    SELECT #{qt target_table}.*, top_n_group_key
    #{join_sql}
    INNER JOIN
    (
      SELECT T.#{q klass.primary_key} as top_n_group_key,
      (
        SELECT #{qt target_table}.#{q order_key}
        #{join_sql}
        WHERE #{qt parent_table}.#{q klass.primary_key} = T.#{q klass.primary_key}
        ORDER BY #{qt target_table}.#{q order_key} #{order_mode.upcase}
        LIMIT 1 OFFSET #{limit.to_i - 1}
      ) AS last_value
      FROM #{qt parent_table} as T where T.#{q klass.primary_key} in (?)
    ) T
    ON #{qt parent_table}.#{q klass.primary_key} = T.top_n_group_key
    AND (
      T.last_value IS NULL
      OR #{qt target_table}.#{q order_key} #{{ asc: :<=, desc: :>= }[order_mode]} T.last_value
      OR #{qt target_table}.#{q order_key} is NULL
    )
  )
end
top_n_group_sql(klass:, group_column:, group_keys:, condition:, limit:, order_mode:, order_key:) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 44
def self.top_n_group_sql(klass:, group_column:, group_keys:, condition:, limit:, order_mode:, order_key:)
  order_op = order_mode == :asc ? :<= : :>=
  group_key_table = value_table(:T, :top_n_group_key, group_keys)
  table_name = klass.table_name
  sql = condition_sql klass, condition
  join_cond = %(#{qt table_name}.#{q group_column} = T.top_n_group_key)
  if group_keys.include? nil
    nil_join_cond = %((#{qt table_name}.#{q group_column} IS NULL AND T.top_n_group_key IS NULL))
    join_cond = %((#{join_cond} OR #{nil_join_cond}))
  end
  %(
    SELECT #{qt table_name}.*, top_n_group_key
    FROM #{qt table_name}
    INNER JOIN
    (
      SELECT top_n_group_key,
      (
        SELECT #{qt table_name}.#{q order_key} FROM #{qt table_name}
        WHERE #{join_cond}
        #{"AND #{sql}" if sql}
        ORDER BY #{qt table_name}.#{q order_key} #{order_mode.to_s.upcase}
        LIMIT 1 OFFSET #{limit.to_i - 1}
      ) AS last_value
      FROM #{group_key_table}
    ) T
    ON #{join_cond}
    AND (
      T.last_value IS NULL
      OR #{qt table_name}.#{q order_key} #{order_op} T.last_value
      OR #{qt table_name}.#{q order_key} is NULL
    )
    #{"WHERE #{sql}" if sql}
  )
end
union_value_table(table, column, values) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 95
def self.union_value_table(table, column, values)
  sanitize_sql_array [
    "(SELECT ? AS #{column}#{' UNION SELECT ?' * (values.size - 1)}) AS #{table}",
    *values
  ]
end
value_table(table, column, values) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 87
def self.value_table(table, column, values)
  if ActiveRecord::Base.connection.adapter_name == 'PostgreSQL'
    values_value_table(table, column, values)
  else
    union_value_table(table, column, values)
  end
end
values_value_table(table, column, values) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 102
def self.values_value_table(table, column, values)
  sanitize_sql_array [
    "(VALUES #{(['(?)'] * values.size).join(',')}) AS #{table} (#{column})",
    *values
  ]
end
where_condition_to_sql(condition) click to toggle source
# File lib/top_n_loader/sql_builder.rb, line 109
def self.where_condition_to_sql(condition)
  case condition
  when String
    condition
  when Array
    sanitize_sql_array condition
  when Hash
    condition.map { |k, v| kv_condition_to_sql k, v }.join ' AND '
  end
end