Module: Earth::Utils

Defined in:
lib/earth/utils.rb

Class Method Summary collapse

Class Method Details

.insert_ignore(args = {}) ⇒ Object

MySQL’s INSERT INTO is not standard SQL ::Earth::Utils.insert_ignore(

:src => AutomobileMakeModelYearVariant,
:dest => AutomobileMakeModelYear,
:cols => {
  :make_model_year_name => :name,
  :make_name => :make_name,
  :name => :model_name,
  :make_model_name => :make_model_name,
  :year => :year,
  :make_year_name => :make_year_name
},
:where => 'LENGTH(src.make_name) > 0 AND LENGTH(src.make_model_name) > 0'

) …executes… INSERT INTO automobile_make_model_years(name,make_name,model_name,make_model_name,year,make_year_name) SELECT MAX(src.make_model_year_name),MAX(src.make_name),MAX(src.name),MAX(src.make_model_name),MAX(src.year),MAX(src.make_year_name) FROM automobile_make_model_year_variants AS src WHERE

src.make_model_year_name IS NOT NULL
AND LENGTH(TRIM(CAST(src.make_model_year_name AS CHAR))) > 0
AND src.make_model_year_name NOT IN (SELECT dest.name FROM automobile_make_model_years AS dest)
AND (1=1)

GROUP BY src.make_model_year_name



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
 
# File 'lib/earth/utils.rb', line 27

def self.insert_ignore(args = {})
  dest_cols = args[:cols].values
  dest_primary_key = args[:dest].primary_key.to_sym
  dest_primary_key_in_src = args[:cols].detect { |k, _| k == dest_primary_key }
  
  sql_items = {}
  args[:cols].each do |src_col, dest_col|
    if src_col.class == Symbol
      sql_items[dest_col] = 'src.' + src_col.to_s
    else
      sql_items[dest_col] = src_col.map { |col| 'src.' + col.to_s }.join(" || ' ' || ")
    end
  end
  
  sql = %{
    INSERT INTO #{args[:dest].table_name}(#{dest_cols.join(',')})
    SELECT #{dest_cols.map { |dest_col| 'MAX(' + sql_items[dest_col] + ')' }.join(',')}
    FROM #{args[:src].table_name} AS src
    WHERE
      (#{sql_items[dest_primary_key]}) IS NOT NULL
      AND LENGTH(TRIM(CAST((#{sql_items[dest_primary_key]}) AS CHAR))) > 0
      AND (#{sql_items[dest_primary_key]}) NOT IN (SELECT dest.#{dest_primary_key} FROM #{args[:dest].table_name} AS dest)
      AND (#{args[:where] || '1=1'})
    GROUP BY #{sql_items[dest_primary_key]}
  }
  ::ActiveRecord::Base.connection.execute sql
end