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



97
98
99
100
101
102
103
104
105
106
107
108
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 97

def apply_cast(expr, cast)
  case cast
  when :integer
    LcpRuby.postgresql? ? "(#{expr})::integer" : "CAST(#{expr} AS INTEGER)"
  when :decimal, :float
    LcpRuby.postgresql? ? "(#{expr})::numeric" : "CAST(#{expr} AS REAL)"
  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



41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 41

def exact_match(scope, table_name, field_name, value)
  validate_field_name!(field_name)
  conn = ActiveRecord::Base.connection
  quoted_table = conn.quote_table_name(table_name)

  condition = if LcpRuby.postgresql?
    "#{quoted_table}.custom_data ->> #{conn.quote(field_name)} = #{conn.quote(value.to_s)}"
  else
    "JSON_EXTRACT(#{quoted_table}.custom_data, #{conn.quote("$.#{field_name}")}) = #{conn.quote(value.to_s)}"
  end

  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



75
76
77
78
79
80
81
82
83
84
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 75

def json_extract_expr(table_name, field_name)
  conn = ActiveRecord::Base.connection
  quoted_table = conn.quote_table_name(table_name)

  if LcpRuby.postgresql?
    "#{quoted_table}.custom_data ->> #{conn.quote(field_name)}"
  else
    "JSON_EXTRACT(#{quoted_table}.custom_data, #{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



61
62
63
64
65
66
67
68
69
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 61

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



30
31
32
33
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 30

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
19
20
21
22
 
# 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
  quoted_table = conn.quote_table_name(table_name)

  if LcpRuby.postgresql?
    "#{quoted_table}.custom_data ->> #{conn.quote(field_name)} ILIKE #{conn.quote("%#{query}%")}"
  else
    "JSON_EXTRACT(#{quoted_table}.custom_data, #{conn.quote("$.#{field_name}")}) LIKE #{conn.quote("%#{query}%")}"
  end
end

.validate_field_name!(field_name) ⇒ Object

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



87
88
89
90
91
 
# File 'lib/lcp_ruby/custom_fields/query.rb', line 87

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