How do I "resuse" a single bind variable in Arel?

Question

I want to programatically create this SQL with single bind parameter with Arel:

"users"."first_name" ILIKE $1 OR "users"."last_name" ILIKE $1

I tried:

users = Arel::Table.new("users")
term = Arel::Nodes::BindParam.new("term") # what is the argument even used for?
users[:first_name].matches(p)
                  .or(
                     users[:last_name].matches(p)
                   )

But it results in two different bind variables:

("users"."first_name" ILIKE $1 OR "users"."last_name" ILIKE $2)

Is there a way to do this or should I just use Arel::Nodes::SqlLiteral.new("$1") instead of BindParam?

Answer 1

BindParam always auto-increments the binding variable, regardless of the argument. When bind node is added here

https://github.com/rails/rails/blob/v7.0.2.3/activerecord/lib/arel/visitors/to_sql.rb#L746

def visit_Arel_Nodes_BindParam(o, collector)
  collector.add_bind(o.value, &bind_block) # o.value # => 'term'
end

the final value just comes from an auto-incremented @bind_index here

https://github.com/rails/rails/blob/v7.0.2.3/activerecord/lib/arel/collectors/sql_string.rb#L15

def add_bind(bind) # bind # => 'term'
  self << yield(@bind_index)
  @bind_index += 1
  self
end

BindParam argument is not used at this point. It's used when other collectors are involved when building full ActiveRecord query, like Arel::Collectors::SubstituteBinds https://github.com/rails/rails/blob/v7.0.2.3/activerecord/lib/arel/collectors/substitute_binds.rb#L18

def add_bind(bind)
  bind = bind.value_for_database if bind.respond_to?(:value_for_database)
  self << quoter.quote(bind)
end

Without making a custom collector class I don't see any other option but Arel::Nodes::SqlLiteral.new("$1").