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 and json_each() subqueries on SQLite.

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_array_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.



45
46
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 45

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_array_length(#{col}) = 0"
    ))
  end

  condition = if LcpRuby.postgresql?
    "#{col} <@ #{pg_array_literal(values, c, item_type)}"
  else
    "NOT EXISTS (SELECT 1 FROM json_each(#{col}) je " \
      "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.



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

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_each(#{col}) je " \
      "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.



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

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_each(#{col}) je " \
      "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_each(#{col}) je " \
      "WHERE je.value LIKE #{quoted_query})"
  end
end