Module: TopNLoader::SQLBuilder

Defined in:
lib/top_n_loader/sql_builder.rb

Class Method Summary collapse

Class Method Details

.adapter_name ⇒ Object 



136
137
138
 
# File 'lib/top_n_loader/sql_builder.rb', line 136

def self.adapter_name
  ActiveRecord::Base.connection.adapter_name
end

.compare_cond(column, order_mode, includes_nil:, t_column: 'T.top_n_order_key') ⇒ Object 



91
92
93
94
95
96
97
98
 
# File 'lib/top_n_loader/sql_builder.rb', line 91

def self.compare_cond(column, order_mode, includes_nil:, t_column: 'T.top_n_order_key')
  op = order_mode == :asc ? '<=' : '>='
  if includes_nil && (nil_first? ? order_mode == :asc : order_mode == :desc)
    "(#{column} #{op} #{t_column} OR #{column} IS NULL OR #{t_column} IS NULL)"
  else
    "(#{column} #{op} #{t_column} OR #{t_column} IS NULL)"
  end
end

.condition_sql(klass, condition) ⇒ Object 



2
3
4
5
6
7
8
9
 
# 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

.equals_cond(column, includes_nil:, t_column: 'T.top_n_group_key') ⇒ Object 



86
87
88
89
 
# File 'lib/top_n_loader/sql_builder.rb', line 86

def self.equals_cond(column, includes_nil:, t_column: 'T.top_n_group_key')
  cond = "#{column} = #{t_column}"
  includes_nil ? "(#{cond} OR (#{column} IS NULL AND #{t_column} IS NULL))" : cond
end

.kv_condition_to_sql(key, value) ⇒ Object 



173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/top_n_loader/sql_builder.rb', line 173

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

.nil_first? ⇒ Boolean

Returns:

  • (Boolean) 


132
133
134
 
# File 'lib/top_n_loader/sql_builder.rb', line 132

def self.nil_first?
  !postgres?
end

.nullable_column?(klass, column) ⇒ Boolean

Returns:

  • (Boolean) 


108
109
110
 
# File 'lib/top_n_loader/sql_builder.rb', line 108

def self.nullable_column?(klass, column)
  klass.column_for_attribute(column).null
end

.postgres? ⇒ Boolean

Returns:

  • (Boolean) 


140
141
142
 
# File 'lib/top_n_loader/sql_builder.rb', line 140

def self.postgres?
  adapter_name == 'PostgreSQL'
end

.q(name) ⇒ Object 



100
101
102
 
# File 'lib/top_n_loader/sql_builder.rb', line 100

def self.q(name)
  ActiveRecord::Base.connection.quote_column_name name
end

.qt(name) ⇒ Object 



104
105
106
 
# File 'lib/top_n_loader/sql_builder.rb', line 104

def self.qt(name)
  ActiveRecord::Base.connection.quote_table_name name
end

.sanitize_sql_array(array) ⇒ Object 



202
203
204
 
# File 'lib/top_n_loader/sql_builder.rb', line 202

def self.sanitize_sql_array(array)
  ActiveRecord::Base.send :sanitize_sql_array, array
end

.sqlite? ⇒ Boolean

Returns:

  • (Boolean) 


144
145
146
 
# File 'lib/top_n_loader/sql_builder.rb', line 144

def self.sqlite?
  adapter_name == 'SQLite'
end

.top_n_association_sql(klass, target_klass, relation, limit:, order_mode:, order_key:) ⇒ Object 



11
12
13
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
 
# 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:)
  limit = limit.to_i
  parent_table = klass.table_name
  joins = klass.joins relation.to_sym
  target_table = target_klass.table_name
  nullable = nullable_column? target_klass, order_key
  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]
  parent_primary_key = "#{qt parent_table}.#{q klass.primary_key}"
  target_order_key = "#{qt target_table}.#{q order_key}"
  target_primary_key = "#{qt target_table}.#{q target_klass.primary_key}"
  top_n_key, top_n_alias, t_join_cond = limit == 1 ? [
    target_primary_key, :top_n_primary_key, "#{target_primary_key} = top_n_primary_key"
  ] : [
    target_order_key, :top_n_order_key, compare_cond(target_order_key, order_mode, includes_nil: nullable)
  ]
  order_columns = limit == 1 ? [target_order_key, target_primary_key].uniq : [target_order_key]
  order_cond = order_columns.map {|column| "#{column} #{order_mode.to_s.upcase}"}.join(', ')
  <<~SQL.squish
    SELECT #{qt target_table}.*, top_n_group_key
    #{join_sql}
    INNER JOIN
    (
      SELECT T.#{q klass.primary_key} AS top_n_group_key,
      (
        SELECT #{top_n_key}
        #{join_sql}
        WHERE #{parent_primary_key} = T.#{q klass.primary_key}
        ORDER BY #{order_cond}
        LIMIT 1#{" OFFSET #{limit - 1}" if limit != 1}
      ) AS #{top_n_alias}
      FROM #{qt parent_table} AS T WHERE T.#{q klass.primary_key} IN (?)
    ) T
    ON #{parent_primary_key} = T.top_n_group_key AND #{t_join_cond}
  SQL
end

.top_n_group_sql(klass:, group_column:, group_keys:, condition:, limit:, order_mode:, order_key:) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
# File 'lib/top_n_loader/sql_builder.rb', line 50

def self.top_n_group_sql(klass:, group_column:, group_keys:, condition:, limit:, order_mode:, order_key:)
  limit = limit.to_i
  table_name = klass.table_name
  sql = condition_sql klass, condition
  group_key_nullable = group_keys.include?(nil) && nullable_column?(klass, group_column)
  order_key_nullable = nullable_column? klass, order_key
  group_key_table = value_table :T, :top_n_group_key, group_keys
  table_order_key = "#{qt table_name}.#{q order_key}"
  join_cond = equals_cond "#{qt table_name}.#{q group_column}", includes_nil: group_key_nullable
  table_primary_key = "#{qt table_name}.#{q klass.primary_key}"
  top_n_key, top_n_alias, t_join_cond = limit == 1 ? [
    table_primary_key, :top_n_primary_key, "#{table_primary_key} = top_n_primary_key"
  ] : [
    table_order_key, :top_n_order_key,
    "#{compare_cond table_order_key, order_mode, includes_nil: order_key_nullable}#{" WHERE #{sql}" if sql}"
  ]
  order_columns = limit == 1 ? [table_order_key, table_primary_key].uniq : [table_order_key]
  order_cond = order_columns.map {|column| "#{column} #{order_mode.to_s.upcase}"}.join(', ')
  <<~SQL.squish
    SELECT #{qt table_name}.*, top_n_group_key
    FROM #{qt table_name}
    INNER JOIN
    (
      SELECT top_n_group_key,
      (
        SELECT #{top_n_key} FROM #{qt table_name}
        WHERE #{join_cond}#{" AND #{sql}" if sql}
        ORDER BY #{order_cond}
        LIMIT 1#{" OFFSET #{limit - 1}" if limit != 1}
      ) AS #{top_n_alias}
      FROM #{group_key_table}
    ) T
    ON #{join_cond} AND #{t_join_cond}
  SQL
end

.type_values(values) ⇒ Object 



112
113
114
115
116
117
 
# File 'lib/top_n_loader/sql_builder.rb', line 112

def self.type_values(values)
  return [nil, values] if sqlite?
  groups = values.group_by { _1.is_a?(Time) || _1.is_a?(DateTime) ? 0 : _1.is_a?(Date) ? 1 : 2 }
  type = groups[0] ? :TIMESTAMP : groups[1] ? :DATE : nil
  [type, groups.sort.flat_map(&:last)]
end

.union_value_table(table, column, values, type) ⇒ Object 



148
149
150
151
152
153
 
# File 'lib/top_n_loader/sql_builder.rb', line 148

def self.union_value_table(table, column, values, type)
  sanitize_sql_array [
    "(SELECT #{"#{type} " if type}? AS #{column}#{' UNION SELECT ?' * (values.size - 1)}) AS #{table}",
    *values
  ]
end

.value_table(table, column, values) ⇒ Object 



119
120
121
122
123
124
125
126
 
# File 'lib/top_n_loader/sql_builder.rb', line 119

def self.value_table(table, column, values)
  type, values = type_values values
  if postgres?
    values_value_table table, column, values, type
  else
    union_value_table table, column, values, type
  end
end

.values_table_batch_size ⇒ Object 



128
129
130
 
# File 'lib/top_n_loader/sql_builder.rb', line 128

def self.values_table_batch_size
  sqlite? ? 200 : 1000
end

.values_value_table(table, column, values, type) ⇒ Object 



155
156
157
158
159
160
 
# File 'lib/top_n_loader/sql_builder.rb', line 155

def self.values_value_table(table, column, values, type)
  sanitize_sql_array [
    "(VALUES (#{"#{type} " if type}?) #{', (?)' * (values.size - 1)}) AS #{table} (#{column})",
    *values
  ]
end

.where_condition_to_sql(condition) ⇒ Object 



162
163
164
165
166
167
168
169
170
171
 
# File 'lib/top_n_loader/sql_builder.rb', line 162

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