Class: WorkerPlugins::AddQuery

Inherits:
ApplicationService show all
Defined in:
app/services/worker_plugins/add_query.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from ApplicationService

#db_now_value, #mysql?, #postgres?, #quote, #quote_column, #quote_table, #sqlite?

Constructor Details

#initialize(query:, workplace:) ⇒ AddQuery

Returns a new instance of AddQuery.



4
5
6
7
8
 
# File 'app/services/worker_plugins/add_query.rb', line 4

def initialize(query:, workplace:)
  @query = query
    .except(:order) # This fixes crashes in Postgres
  @workplace = workplace
end

Instance Attribute Details

#query ⇒ Object (readonly)

Returns the value of attribute query.



2
3
4
 
# File 'app/services/worker_plugins/add_query.rb', line 2

def query
  @query
end

#workplace ⇒ Object (readonly)

Returns the value of attribute workplace.



2
3
4
 
# File 'app/services/worker_plugins/add_query.rb', line 2

def workplace
  @workplace
end

Instance Method Details

#add_query_to_workplace ⇒ Object 



16
17
18
 
# File 'app/services/worker_plugins/add_query.rb', line 16

def add_query_to_workplace
  WorkerPlugins::WorkplaceLink.connection.execute(sql)
end

#created ⇒ Object 



20
21
22
 
# File 'app/services/worker_plugins/add_query.rb', line 20

def created
  @created ||= resources_to_add.pluck(primary_key.to_sym)
end

#existing_workplace_link_exists_sql ⇒ Object 



58
59
60
61
62
63
64
65
66
67
 
# File 'app/services/worker_plugins/add_query.rb', line 58

def existing_workplace_link_exists_sql
  resource_id_column = "#{quote_table(WorkerPlugins::WorkplaceLink.table_name)}.#{quote_column(:resource_id)}"

  workplace
    .workplace_links
    .where(resource_type: model_class.name)
    .where("#{resource_id_column} = #{model_primary_key_cast_for_resource_id}")
    .select(1)
    .to_sql
end

#ids_added_already ⇒ Object 



30
31
32
33
34
35
36
 
# File 'app/services/worker_plugins/add_query.rb', line 30

def ids_added_already
  WorkerPlugins::SelectColumnWithTypeCast.execute!(
    column_name_to_select: :resource_id,
    column_to_compare_with: model_class.column_for_attribute(:id),
    query: ids_added_already_query
  )
end

#ids_added_already_query ⇒ Object 



24
25
26
27
28
 
# File 'app/services/worker_plugins/add_query.rb', line 24

def ids_added_already_query
  workplace
    .workplace_links
    .where(resource_type: model_class.name)
end

#model_class ⇒ Object 



38
39
40
 
# File 'app/services/worker_plugins/add_query.rb', line 38

def model_class
  @model_class ||= query.klass
end

#model_primary_key_cast_for_resource_id ⇒ Object 



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'app/services/worker_plugins/add_query.rb', line 69

def model_primary_key_cast_for_resource_id
  primary_key_column = "#{quote_table(model_class.table_name)}.#{quote_column(model_class.primary_key)}"

  # MySQL and SQLite do implicit conversion when comparing integer/uuid/string
  # primary keys to the `resource_id` VARCHAR column. Postgres is strict about
  # types and needs an explicit cast.
  return primary_key_column unless postgres?

  primary_key_type = model_class.column_for_attribute(model_class.primary_key).type
  resource_id_type = WorkerPlugins::WorkplaceLink.column_for_attribute(:resource_id).type

  return primary_key_column if primary_key_type == resource_id_type

  "CAST(#{primary_key_column} AS VARCHAR)"
end

#perform ⇒ Object 



10
11
12
13
14
 
# File 'app/services/worker_plugins/add_query.rb', line 10

def perform
  created # Cache which are about to be created
  add_query_to_workplace
  succeed!(created:)
end

#primary_key ⇒ Object 



42
43
44
 
# File 'app/services/worker_plugins/add_query.rb', line 42

def primary_key
  @primary_key ||= resources_to_add.klass.primary_key
end

#resources_to_add ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
 
# File 'app/services/worker_plugins/add_query.rb', line 46

def resources_to_add
  # Correlate per row with NOT EXISTS instead of NOT IN + a materialized
  # subquery. The old form expanded into a nested `resource_id IN (SELECT
  # CAST(users.id AS CHAR) FROM users)` that did a full scan of the target
  # table when the outer query was unfiltered — 60s+ on 340k+ users. This
  # uses the `(workplace_id, resource_type, resource_id)` composite index
  # for an index seek per row.
  @resources_to_add ||= query
    .distinct
    .where("NOT EXISTS (#{existing_workplace_link_exists_sql})")
end

#select_sql ⇒ Object 



85
86
87
88
89
90
91
92
93
94
95
 
# File 'app/services/worker_plugins/add_query.rb', line 85

def select_sql
  @select_sql ||= resources_to_add
    .select("
      #{db_now_value},
      #{quote(resources_to_add.klass.name)},
      #{quote_table(resources_to_add.klass.table_name)}.#{quote_column(primary_key)},
      #{db_now_value},
      #{select_workplace_id_sql}
    ")
    .to_sql
end

#select_workplace_id_sql ⇒ Object 



97
98
99
100
101
102
103
104
105
 
# File 'app/services/worker_plugins/add_query.rb', line 97

def select_workplace_id_sql
  workplace_id_column = WorkerPlugins::WorkplaceLink.columns.find { |column| column.name == "workplace_id" }

  if workplace_id_column.type == :uuid
    "CAST(#{quote(workplace.id)} AS UUID)"
  else
    quote(workplace.id)
  end
end

#sql ⇒ Object 



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'app/services/worker_plugins/add_query.rb', line 107

def sql
  @sql ||= "
    INSERT INTO
      worker_plugins_workplace_links

    (
      created_at,
      resource_type,
      resource_id,
      updated_at,
      workplace_id
    )

    #{select_sql}
  "
end