Class: LcpRuby::ArrayQuery

Inherits:
Object
  • Object
show all
Defined in:
lib/lcp_ruby/array_query.rb

Overview

DB-portable query helpers for array fields. Uses native PG array operators (@>, &&, <@) on PostgreSQL, a json_each() subquery on SQLite, and a JSON_TABLE() row source on MySQL/MariaDB (which has no json_each). Both non-PG backends expose each element as ‘je.value`, so the conditions below are shared.

Class Method Summary collapse

Class Method Details

.array_length_expression(table_name, field_name) ⇒ Object

SQL expression for the size of the array.



70
71
72
73
74
75
76
77
78
79
 
# File 'lib/lcp_ruby/array_query.rb', line 70

def array_length_expression(table_name, field_name)
  c = connection
  col = quoted_column(c, table_name, field_name)

  if LcpRuby.postgresql?
    "COALESCE(array_length(#{col}, 1), 0)"
  else
    json_length(col)
  end
end

.contained_by(scope, table_name, field_name, values, item_type: "string") ⇒ Object

Records where the array field is a subset of the given values. Empty values matches only records with empty arrays.



47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/lcp_ruby/array_query.rb', line 47

def contained_by(scope, table_name, field_name, values, item_type: "string")
  values = Array(values)

  c = connection
  col = quoted_column(c, table_name, field_name)

  if values.empty?
    return scope.where(Arel.sql(
      LcpRuby.postgresql? ? "#{col} = '{}'" : "#{json_length(col)} = 0"
    ))
  end

  condition = if LcpRuby.postgresql?
    "#{col} <@ #{pg_array_literal(values, c, item_type)}"
  else
    "NOT EXISTS (SELECT 1 FROM #{json_elements(col)} " \
      "WHERE je.value NOT IN (#{quoted_values(values, c)}))"
  end

  scope.where(Arel.sql(condition))
end

.contains(scope, table_name, field_name, values, item_type: "string") ⇒ Object

Records where the array field contains ALL of the given values.



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'lib/lcp_ruby/array_query.rb', line 10

def contains(scope, table_name, field_name, values, item_type: "string")
  values = Array(values)
  return scope if values.empty?

  c = connection
  col = quoted_column(c, table_name, field_name)

  condition = if LcpRuby.postgresql?
    "#{col} @> #{pg_array_literal(values, c, item_type)}"
  else
    "(SELECT COUNT(DISTINCT je.value) FROM #{json_elements(col)} " \
      "WHERE je.value IN (#{quoted_values(values, c)})) = #{values.size}"
  end

  scope.where(Arel.sql(condition))
end

.overlaps(scope, table_name, field_name, values, item_type: "string") ⇒ Object

Records where the array field contains ANY of the given values.



28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'lib/lcp_ruby/array_query.rb', line 28

def overlaps(scope, table_name, field_name, values, item_type: "string")
  values = Array(values)
  return scope.none if values.empty?

  c = connection
  col = quoted_column(c, table_name, field_name)

  condition = if LcpRuby.postgresql?
    "#{col} && #{pg_array_literal(values, c, item_type)}"
  else
    "EXISTS (SELECT 1 FROM #{json_elements(col)} " \
      "WHERE je.value IN (#{quoted_values(values, c)}))"
  end

  scope.where(Arel.sql(condition))
end

.text_search_condition(table_name, field_name, query) ⇒ Object

Text search condition for array string fields (used by QuickSearch).



82
83
84
85
86
87
88
89
90
91
92
93
94
 
# File 'lib/lcp_ruby/array_query.rb', line 82

def text_search_condition(table_name, field_name, query)
  c = connection
  col = quoted_column(c, table_name, field_name)
  quoted_query = c.quote("%#{query}%")

  if LcpRuby.postgresql?
    "EXISTS (SELECT 1 FROM unnest(#{col}) item " \
      "WHERE item ILIKE #{quoted_query})"
  else
    "EXISTS (SELECT 1 FROM #{json_elements(col)} " \
      "WHERE je.value LIKE #{quoted_query})"
  end
end