Search code examples
ruby-on-railsarel

How to handle normalized attribute stripping away % characters when building LIKE query in arel?


Setup:

rails new testing --minimal
cd testing

bin/rails g model Property name handle --no-timestamps
bin/rails db:migrate
sed -i '2i \  normalizes :handle, with: ->(handle) { handle.to_s.underscore.parameterize(separator: "_") }' app/models/property.rb
# app/models/property.rb

class Property < ApplicationRecord
  normalizes :handle, with: ->(handle) { handle.to_s.underscore.parameterize(separator: "_") }
end

handle normalization is expected when creating/updating a record and when searching:

Property.create(name: "My property", handle: "  My   property ")
#=> #<Property:0x00007f9b3a0e2e50 id: 1, name: "My property", handle: "my_property">
#                                                                      ^^^^^^^^^^^

Property.where(handle: "  myProperty  ").to_sql
#=> "SELECT \"properties\".* FROM \"properties\" WHERE \"properties\".\"handle\" = 'my_property'"
#                                                                                   ^^^^^^^^^^^

But when building a LIKE query with arel the % characters are also stripped away, which should be part of the query:

Property.arel_table[:handle].matches("%prop%").to_sql
#=> "\"properties\".\"handle\" LIKE 'prop'"
#                                    ^^^^

Can I avoid doing where("handle LIKE ?") and always keep handle normalized?


Solution

  • This problem was raised in this Rails issue. Looks like there is no plan to fix this as it's not part of the public API.

    You can get around the wildcard getting filtered with a quoted arel node (which doesn't get cast):

    search_condition = Arel::Nodes::Quoted.new("%prop%")
    Property.arel_table[:handle].matches(search_condition).to_sql
    # => "\"properties\".\"handle\" LIKE '%prop%'"   
    

    And if you want to apply the normalization before searching:

    normalized_term = Property.normalize_value_for(:handle, "PROP EXAMPLE") # => "prop_example"
    search_condition = Arel::Nodes::Quoted.new("%#{normalized_term}%")
    Property.arel_table[:handle].matches(search_condition).to_sql
    # => "\"properties\".\"handle\" LIKE '%prop_example%'"