Module: HasHelpers::DN::Utils::QueryUtils

Included in:
Processor
Defined in:
app/lib/has_helpers/dn/utils/query_utils.rb

Instance Method Summary collapse

Instance Method Details

#fetch_update_info(column:, table_to_change_info:, table_name:, class_name:) ⇒ Object 



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 8

def fetch_update_info(column:, table_to_change_info:, table_name:, class_name:)
  table_to_change_info = table_to_change_info&.symbolize_keys
  table_to_change = table_to_change_info[:dependent_table].to_s
  column_to_change = table_to_change_info[:column_name].to_s
  foreign_key = table_to_change_info[:foreign_key]&.to_s
  table_with_changes_included = table_to_change_info[:table_with_changes].present?
  table_with_changes = table_to_change_info[:table_with_changes].present? ? table_to_change_info[:table_with_changes].symbolize_keys[:table].to_s : table_name
  association = class_name.reflect_on_all_associations.find { |assoc| assoc.plural_name == (table_to_change_info[:alias] ? table_to_change_info[:alias].to_s : table_to_change) }
  reverse_association = nil
  if table_name != table_to_change
    class_to_change = table_to_change&.classify&.safe_constantize
    class_to_change = ActiveRecord::Base.descendants.find { |klass| klass.table_name == table_to_change } if class_to_change.nil?
    reverse_association = class_to_change&.reflect_on_all_associations&.find { |assoc| assoc.plural_name == table_name }
  end
  column_with_changes =
    if table_to_change_info[:table_with_changes].present?
      table_to_change_info[:table_with_changes].symbolize_keys[:column].to_s
    elsif table_to_change_info[:column_with_changes].present?
      table_to_change_info[:column_with_changes].to_s
    else
      column
    end

  inner_association = class_name.reflect_on_all_associations.find { |assoc| assoc.plural_name == table_with_changes } if table_to_change_info[:table_with_changes].present?
  { table_name: table_name, column: column, table_to_change: table_to_change, column_to_change: column_to_change, table_with_changes: table_with_changes, column_with_changes: column_with_changes, association: association, reverse_association: reverse_association, inner_association: inner_association, foreign_key: foreign_key, table_with_changes_included: table_with_changes_included }
end

#update_assoc(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:, relation_type:) ⇒ Object 



174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 174

def update_assoc(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:, relation_type:)
  where_clause =
    if relation_type == "one_to_many"
      "#{table_with_changes}.id = #{table_to_change}.#{foreign_key}"
    else
      "#{table_with_changes}.#{foreign_key} = #{table_to_change}.id"
    end
  where_clause += " AND #{table_with_changes}.id = '#{record_id}'"
  where_clause += " AND #{table_with_changes}.is_primary = TRUE" if relation_type == "many_to_one"
  <<-SQL
    UPDATE
      #{table_to_change}
    SET
      #{column_to_change} = #{table_with_changes}.#{column_with_changes}
    FROM
      #{table_with_changes}
    WHERE
      #{where_clause}
    RETURNING #{table_with_changes}.id
  SQL
end

#update_intermediate_of_many_to_many(record_id:, table_name:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:, inner_foreign_key:) ⇒ Object 



196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 196

def update_intermediate_of_many_to_many(record_id:, table_name:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:, inner_foreign_key:)
  <<-SQL
    UPDATE
      #{table_to_change}
    SET
      #{column_to_change} = #{table_with_changes}.#{column_with_changes}
    FROM
      #{table_name}
    LEFT JOIN
      #{table_with_changes} ON #{table_with_changes}.id = #{table_name}.#{inner_foreign_key}
    WHERE
      #{table_name}.id = '#{record_id}' AND
      #{table_name}.#{foreign_key} = #{table_to_change}.id AND
      #{table_name}.is_primary = TRUE
    RETURNING #{table_to_change}.id
  SQL
end

#update_many_to_many(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, association:) ⇒ Object 



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 138

def update_many_to_many(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, association:)
  <<-SQL
    UPDATE
      #{table_to_change}
    SET
      #{column_to_change} = #{table_with_changes}.#{column_with_changes}
    FROM
      #{association.options[:through]}
    INNER JOIN
      #{table_with_changes} ON #{table_with_changes}.id = #{association.options[:through]}.#{association.through_reflection.foreign_key}
    WHERE
      #{table_with_changes}.id = '#{record_id}' AND
      #{association.options[:through]}.is_primary = TRUE AND
      #{association.options[:through]}.#{association.foreign_key} = #{table_to_change}.id
    RETURNING #{table_to_change}.id
  SQL
end

#update_many_to_one(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:) ⇒ Object 



168
169
170
171
172
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 168

def update_many_to_one(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:)
  update_assoc(record_id: record_id, table_to_change: table_to_change, column_to_change: column_to_change,
               table_with_changes: table_with_changes, column_with_changes: column_with_changes,
               foreign_key: foreign_key, relation_type: "many_to_one")
end

#update_one_to_many(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:) ⇒ Object 



156
157
158
159
160
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 156

def update_one_to_many(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:)
  update_assoc(record_id: record_id, table_to_change: table_to_change, column_to_change: column_to_change,
               table_with_changes: table_with_changes, column_with_changes: column_with_changes,
               foreign_key: foreign_key, relation_type: "one_to_many")
end

#update_one_to_one(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:) ⇒ Object 



162
163
164
165
166
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 162

def update_one_to_one(record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:, foreign_key:)
  update_assoc(record_id: record_id, table_to_change: table_to_change, column_to_change: column_to_change,
               table_with_changes: table_with_changes, column_with_changes: column_with_changes,
               foreign_key: foreign_key, relation_type: "one_to_one")
end

#update_sql(options = {}) ⇒ Object 



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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 35

def update_sql (options = {})
  table_name = options[:table_name]
  column = options[:column]
  table_to_change = options[:table_to_change]
  column_to_change = options[:column_to_change]
  table_with_changes = options[:table_with_changes]
  column_with_changes = options[:column_with_changes]
  association = options[:association]
  reverse_association = options[:reverse_association]
  inner_association = options[:inner_association]
  foreign_key = options[:foreign_key]
  record_id = options[:record_id]
  table_with_changes_included = options[:table_with_changes_included]
  # table_name --> table that changes and triggers the refresh. Ex: advisors
  # column --> column of the table that changed. Ex: column= name in table advisors
  # table_to_change --> table that will be changed. Ex: licenses
  # column_to_change --> column on the table_to_change that will be updated. Ex advisor_name on licenses
  # Continuing with the example: Changes on the column "name" of the table "advisors" will cause
  # changes on the colum "advisor_name" on the table "licenses"
  # table_with_changes --> table that contains the changes. (in the previous example its the same table, but there could be a case that changes are not on the same table)
  # association--> association between the class of the table that was changed and the one that should be changed
  # reverse_association --> reverse association between the class of the table that was changed and the one that should be changed
  # we use the association and the reverse_association to determine the type of relationship between resources
  # foreign_key --> foreign key between relationships
  # alias --> the name we use to find the class that a resource is associated with, we need to do that to the relationship type. Ex: Between Contract Levels and Levels we need CarrierLevels.

  association_type = association&.macro
  reverse_association_type = reverse_association&.macro
  foreign_key ||= reverse_association_type == :has_one ? reverse_association&.foreign_key : association&.foreign_key
  is_many_to_many = association_type == :has_many && association.options[:through].present?
  is_one_to_one = association_type == :belongs_to && reverse_association_type == :has_one
  is_many_to_one = association_type == :belongs_to && inner_association.nil?
  is_one_to_many = (association_type == :has_many || association_type == :has_one) && !table_with_changes_included

  # depending on the type of relationship we have different sql updates
  # 1.changes on the same table. Ex: when we change advisor_id on the license table we need to update the advisor_name column on the license table as well
  # 2.changes on records of has many to many relationship (has many trough). Ex: when you change the advisor name of an advisor that belong to a writing adv of an oppty,
  #   In this case we need to change the primary_advisor of the opportunities table.
  #   For this types of relationships we only update the primary record (normally specified with an is_primary column)
  # 3.Changes on records of relationship has_many. Ex: when we update the name of an advisor we need to update the column advisor_name of all the associated
  #    licenses.
  # 4. Changes on records with one to one relationship. (belongs_to and has one) Ex: demographics and advisors.
  # 5.Changes on records with a belongs_to relationship. Ex: when we update the number of a phone of an advisor. In this case we need to change the primary_number column
  #   on the advisors tables. For this types of relationships we only update the primary record (normally specified with an is_primary column)
  # 6. When we are updating an intermediary table of many to many relationship. Ex: when you update a writing_advisor of an oppty (change the advisor or the is_primary)

  upsert_sql =
    if table_to_change == table_name
      # Case 1
      update_table(column: column, record_id: record_id, table_to_change: table_to_change,
                   column_to_change: column_to_change, table_with_changes: table_with_changes,
                   column_with_changes: column_with_changes)
    elsif is_many_to_many
      # Case 2
      update_many_to_many(record_id: record_id, table_to_change: table_to_change,
                          column_to_change: column_to_change, table_with_changes: table_with_changes,
                          column_with_changes: column_with_changes, association: association)
    elsif is_one_to_many
      # case 3
      update_one_to_many(record_id: record_id, table_to_change: table_to_change,
                         column_to_change: column_to_change, table_with_changes: table_with_changes,
                         column_with_changes: column_with_changes, foreign_key: foreign_key)
    elsif is_one_to_one
      # case 4
      # when we include the hash table_with_changes then we are updating intermediary table, which is the last case
      update_one_to_one(record_id: record_id, table_to_change: table_to_change,
                        column_to_change: column_to_change, table_with_changes: table_with_changes,
                        column_with_changes: column_with_changes, foreign_key: foreign_key)
    elsif is_many_to_one
      # case 5
      update_many_to_one(record_id: record_id, table_to_change: table_to_change,
                         column_to_change: column_to_change, table_with_changes: table_with_changes,
                         column_with_changes: column_with_changes, foreign_key: foreign_key)
    else
      # in case where we are updating an intermediary table of many-to-many relationship
      # Case 6
      inner_foreign_key = inner_association&.foreign_key
      update_intermediate_of_many_to_many(record_id: record_id, table_name: table_name, table_to_change: table_to_change,
                                          column_to_change: column_to_change, table_with_changes: table_with_changes,
                                          column_with_changes: column_with_changes, foreign_key: foreign_key,
                                          inner_foreign_key: inner_foreign_key)
    end

  upsert_sql
end

#update_table(column:, record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:) ⇒ Object 



121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
 
# File 'app/lib/has_helpers/dn/utils/query_utils.rb', line 121

def update_table(column:, record_id:, table_to_change:, column_to_change:, table_with_changes:, column_with_changes:)
  <<-SQL
    UPDATE
      #{table_to_change}
    SET
      #{column_to_change} =
      (
        SELECT #{table_with_changes}.#{column_with_changes} FROM #{table_with_changes}
        LEFT OUTER JOIN #{table_to_change} ON
        #{table_to_change}.#{column} = #{table_with_changes}.id
        WHERE #{table_to_change}.id = '#{record_id}'
      )
    WHERE #{table_to_change}.id = '#{record_id}'
    RETURNING #{table_to_change}.id
  SQL
end