CODEWITHSUNDEEP
sqlruby-on-railsruby
jdesilvio
jdesilvio

Reputation: 1854

SQL query in Rails using % and LIKE

I am trying to execute a SQL query in my rails app. The following executes normally since it is looking for an exact match:

connection.query("SELECT * 
                  FROM test
                  WHERE y=#{connection.quote(name)}
                  ORDER BY x ASC")

I want to use the LIKE operator to find partial matches. In SQL it would look like:

SELECT * FROM test WHERE y LIKE '%John%' ORDER BY x ASC

How do I do this in my Ruby query? I tried adding % in a few places but it doesn't work. I get errors that say my query is looking for %'John'% instead of '%John%'

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE #{%connection.quote(name)%}
                  ORDER BY x ASC")

Upvotes: 1

Views: 2080

Answers (3)

spickermann
spickermann

Reputation: 106952

Since you use Rails anyway I suggest using ActiveRecord's query interface instead of plain SQL.

With ActiveRecord the query could be written like this:

Test.where("y LIKE ?", "%#{name}%").order(:x)

You need to have an ActiveRecord model named Test that is configured to use a database table named test (Rails default naming would be tests) to make this work:

# in app/models/test.rb
class Test < ActiveRecord::Base
  self.table_name = 'test'
end

Upvotes: 6

mu is too short
mu is too short

Reputation: 434705

You need to add the %s in Ruby before quoting:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE #{connection.quote('%' + name + '%')}
                  ORDER BY x ASC")

connection.quote will add single quotes to produce a valid SQL string literal and you want to get the %s inside that string literal, hence the Ruby string concatenation before connection.quote is called.

Or you could do it in SQL:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE '%' || #{connection.quote(name)} || '%'
                  ORDER BY x ASC")

|| is the standard SQL string concatenation operator, you might need to use the concat function or something else if you're using a database that doesn't really support SQL.

You're better off using the ActiveRecord interface as spickermann suggests but sometimes you need to do it by hand so it is useful to know how.

Upvotes: 0

steve klein
steve klein

Reputation: 2629

You want:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE '%#{connection.quote(name)}%'
                  ORDER BY x ASC")

Keep in mind that Ruby variable interpolation just replaces #{foo} with the value of foo, no more, no less. When you add the extra % inside the brackets, you are asking Ruby to evaluate %foo%, which is not a valid Ruby variable.

Upvotes: 1

Related Questions