Class: LcpRuby::CustomFields::Query

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

Constant Summary collapse

VALID_FIELD_NAME = 
/\A[a-z][a-z0-9_]*\z/

Class Method Summary collapse

Class Method Details

.apply_cast(expr, cast) ⇒ String

Apply a type cast to a SQL expression.

Parameters:

  • expr (String) —

    SQL expression

  • cast (Symbol) —

    one of :integer, :decimal, :float, :date

Returns:

  • (String) —

    cast expression



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
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 92

def apply_cast(expr, cast)
  case cast
  when :integer
    if LcpRuby.postgresql?
      "(#{expr})::integer"
    elsif LcpRuby.mysql?
      # MySQL/MariaDB CAST has no INTEGER/REAL target — use SIGNED / DECIMAL.
      "CAST(#{expr} AS SIGNED)"
    else
      "CAST(#{expr} AS INTEGER)"
    end
  when :decimal, :float
    if LcpRuby.postgresql?
      "(#{expr})::numeric"
    elsif LcpRuby.mysql?
      "CAST(#{expr} AS DECIMAL(65, 30))"
    else
      "CAST(#{expr} AS REAL)"
    end
  when :date
    LcpRuby.postgresql? ? "(#{expr})::date" : "DATE(#{expr})"
  else
    expr
  end
end

.exact_match(scope, table_name, field_name, value) ⇒ ActiveRecord::Relation

Exact match condition for a custom field value.

Parameters:

  • scope (ActiveRecord::Relation) —

    the current scope

  • table_name (String) —

    the database table name

  • field_name (String) —

    the custom field name

  • value (Object) —

    the value to match

Returns:

  • (ActiveRecord::Relation) —

    filtered scope



37
38
39
40
41
42
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 37

def exact_match(scope, table_name, field_name, value)
  validate_field_name!(field_name)
  conn = ActiveRecord::Base.connection
  condition = "#{json_extract_expr(table_name, field_name)} = #{conn.quote(value.to_s)}"
  scope.where(Arel.sql(condition))
end

.json_extract_expr(table_name, field_name) ⇒ String

Build a DB-portable JSON extraction expression for custom_data column.

Parameters:

  • table_name (String) —

    the database table name

  • field_name (String) —

    the custom field name (key within custom_data)

Returns:

  • (String) —

    SQL expression fragment



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 64

def json_extract_expr(table_name, field_name)
  conn = ActiveRecord::Base.connection
  col = "#{conn.quote_table_name(table_name)}.custom_data"

  if LcpRuby.postgresql?
    "#{col} ->> #{conn.quote(field_name)}"
  elsif LcpRuby.mysql?
    # MySQL/MariaDB JSON_EXTRACT keeps the JSON quotes around scalars
    # ("5", "text"), which breaks numeric CAST (-> 0) and LIKE matching.
    # JSON_UNQUOTE strips them. SQLite's json_extract already returns the
    # raw scalar and has no JSON_UNQUOTE function, so it keeps the bare form.
    "JSON_UNQUOTE(JSON_EXTRACT(#{col}, #{conn.quote("$.#{field_name}")}))"
  else
    "JSON_EXTRACT(#{col}, #{conn.quote("$.#{field_name}")})"
  end
end

.sort_expression(table_name, field_name, direction, cast: nil) ⇒ Arel::Nodes::SqlLiteral

Build a sort expression for ordering by a custom field value.

Parameters:

  • table_name (String) —

    the database table name

  • field_name (String) —

    the custom field name

  • direction (String) —

    “asc” or “desc”

  • cast (Symbol, nil) (defaults to: nil) —

    optional type cast (:integer, :decimal, :date)

Returns:

  • (Arel::Nodes::SqlLiteral) —

    SQL sort expression



50
51
52
53
54
55
56
57
58
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 50

def sort_expression(table_name, field_name, direction, cast: nil)
  validate_field_name!(field_name)
  dir = direction.to_s.downcase == "desc" ? "DESC" : "ASC"

  expr = json_extract_expr(table_name, field_name)
  expr = apply_cast(expr, cast) if cast

  Arel.sql("#{expr} #{dir}")
end

.text_search(scope, table_name, field_name, query) ⇒ ActiveRecord::Relation

Apply a text search on a scope for a custom field.

Parameters:

  • scope (ActiveRecord::Relation) —

    the current scope

  • table_name (String) —

    the database table name

  • field_name (String) —

    the custom field name

  • query (String) —

    the search term (already sanitized)

Returns:

  • (ActiveRecord::Relation) —

    filtered scope



26
27
28
29
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 26

def text_search(scope, table_name, field_name, query)
  condition = text_search_condition(table_name, field_name, query)
  scope.where(Arel.sql(condition))
end

.text_search_condition(table_name, field_name, query) ⇒ String

Generate a text search condition for a custom field stored in custom_data JSONB/JSON column.

Parameters:

  • table_name (String) —

    the database table name

  • field_name (String) —

    the custom field name (key within custom_data)

  • query (String) —

    the search query (already sanitized with sanitize_sql_like)

Returns:

  • (String) —

    SQL condition fragment



12
13
14
15
16
17
18
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 12

def text_search_condition(table_name, field_name, query)
  validate_field_name!(field_name)
  conn = ActiveRecord::Base.connection
  expr = json_extract_expr(table_name, field_name)
  op = LcpRuby.postgresql? ? "ILIKE" : "LIKE"
  "#{expr} #{op} #{conn.quote("%#{query}%")}"
end

.validate_field_name!(field_name) ⇒ Object

Validate that a custom field name is safe for use in SQL expressions.



82
83
84
85
86
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 82

def validate_field_name!(field_name)
  unless field_name.to_s.match?(VALID_FIELD_NAME)
    raise ArgumentError, "Invalid custom field name: #{field_name.inspect}"
  end
end