How to use `matches()` with a database function?

Question

I had an ActiveRecord scope like this:

scope :matching, ->(query) {
  where Post.arel_table[:title].matches "%#{query}%"
}

This worked fine. I then changed it to ignore extra whitespace in the title:

scope :matching, ->(query) {
  where "regexp_replace(title, '\\s+', ' ', 'g') ILIKE ?", "%#{query}%"
}

This works but I have had to drop down from matches() (at the Ruby level) to ILIKE (at the SQL level).

Is it possible to use regexp_replace(...) with matches()? For example:

scope :matching, ->(query) {
  where handwaving("regexp_replace(title, '\\s+', ' ', 'g')").matches "%#{query}%"
}

(I tried to use an Arel::Nodes::NamedFunction but couldn't get it to work.)

Answer 1

Yes you can construct the Arel::Nodes::NamedFunction as follows:

condition = Arel::Nodes::NamedFunction.new(
   'regex_replace', 
   [Post.arel_attribute(:title),
    Arel.sql("'\\s+'"),
    Arel.sql("' '"), 
    Arel.sql("'g'")]
).matches("%#{query}%")

Then

scope :matching, ->(query) {
  # above condition code
  where(condition)
}

This will result in the following (query = 'testing')

SELECT 
  posts.*
FROM 
  posts
WHERE 
  regex_replace(posts.title, '\\s+',' ','g') ILIKE '%testing%'