How to use LIKE instead ILIKE on PostgreSQL?

[glaucocustodio]

Hi folks, first I would like to thank you all for the great work!

I need to use LIKE instead ILIKE (for performance reasons) when using cont predicate. I've checked https://github.com/rails/arel/blob/master/lib/arel/predications.rb#L121 and I know that we can turn the search case sensitive just by passing case_sensitive = true to the matches method.

I am creating a new predicate and the add_predicate method has the option arel_predicate that seems to only accept a string.

Is that possible to pass case_sensitive = true when using the matches predicate?

I am using arel (6.0.3).

Thank in advance.

[glaucocustodio]

The case_sensitive option I've mentioned is only available as of arel version 7.0.0 and I still need to stay with the 6.0.3 version.

To get what I needed, I created a predicate (note I am using the s_matches predicate that does not exist):

# config/initializers/ransack.rb
config.add_predicate 'like', arel_predicate: 's_matches', formatter: proc { |v| "%#{v}%" }, type: :string

Then I monkey patched the arel gem to support the case_sensitive option and to add the s_matches predicate:

module Arel
  module Nodes
    class Matches < Binary
      attr_reader :escape
      attr_accessor :case_sensitive

      def initialize(left, right, escape = nil, case_sensitive = false)
        super(left, right)
        @escape = escape && Nodes.build_quoted(escape)
        @case_sensitive = case_sensitive
      end
    end

    class DoesNotMatch < Matches; end
  end
end

module Arel
  module Predications
    def s_matches(other, escape = nil, case_sensitive = true)
      Nodes::Matches.new self, quoted_node(other), escape, case_sensitive
    end
  end
end

module Arel
  module Visitors
    class PostgreSQL < Arel::Visitors::ToSql
      private

      def visit_Arel_Nodes_Matches o, collector
        op = o.case_sensitive ? ' LIKE ' : ' ILIKE '
        collector = infix_value o, collector, op
        if o.escape
          collector << ' ESCAPE '
          visit o.escape, collector
        else
          collector
        end
      end
    end
  end
end

Now in any view I can use my like predicate:

= f.input :name_like

[glaucocustodio]

But I think would be nice for ransack to have two versions of cont/matches (case sensitive and insensitive), something like: matches/i_matches and i_cont/cont. The problem with this approach is that not all databases support ILIKE I believe. But at least the databases that support both LIKE and ILIKE could have some way (through ransack api) to distinct between them.

[jonatack]

Hi @glaucocustodio, thanks for posting your solution. Concerning the feature request, if you make a PR I'd be happy to review it for merge. Another contributor started work on i_cont / i_not_cont predicates (see the wiki here) but it wasn't completed. I don't recall off-hand what the issue was.

[davidwparker]

Too bad this was never implemented.

When using the pg_trgm extension with PG and lower(), I can get identical results in ~90ms compared to ~2500ms using ILIKE.

[abhishek77in]

@glaucocustodio Any solution to this issue? Without the monkey patching?

[abhishek77in]

@glaucocustodio Followed your code to Monkey patch, done, thanks. I understand monkey patching arel is the only solution here.