Rails: How to find_by a field containing a certain string

Question

I have a model named Topic, that has a name as a field.

So say I have a term I'm searching for, apple.

If I do a

Topic.find_by_name("apple")

I get a record back with the name apple. That's good -- but how do I change find_by_name so that it can find "apple juice" as well as "apple" -- basically, find names that contain the original query or exactly match the original query?

Edit: Thanks for all the response. I guess I should've been a little more clear earlier, but what if I want to find by a variable name (obviously I'm not going to want to find by the name "apple" everytime :) )?

How do I manipulate Topic.where to accommodate for this? So something like...

@topic = Topic.where(......., @name)

Slosh

Added an edit to my answer related to your latest edit, let me know if that helps! – Indium

See my comment to @Alisher's answer for answer to your edited question. Topic.where("name like ?","#{@name}%") would be one way. – Kneehigh

Answer 1

I think something like this should work:

Topic.where("name like ?", "%apple%")

To accomodate for your edit:

Topic.where("name like ?", "%#{@search}%")

Basic string interpolation, you're using the value of @search inside the string %%, so you @search = "apple" then you end up with %apple%

Indium

Do you think this method leads to SQL injection? – Refractometer

As far as I can tell here the where method does nothing to protect against SQL injection. However, the question con only truly be answered by considering if that variable is ever exposed to user input, either through a stored unsanitized value, or directly. That should be the question of interest. The best way to counter SQL injection is to sanitize values upon application entry, even before storage. – Lefthander

how about not contain a certain string? – Gnathic

Rails prevents SQL injection as long as you use the ? for the argument as shown above. You'd be left open if you did "name like %@search%" instead. – Weiss

@Weiss is right as stated in guides.rubyonrails.org/… and guides.rubyonrails.org/security.html#sql-injection – Event

Answer 2

With PostgreSQL you can also use match operators:

Topic.where("name ~* ?", @search)

Chyle

Answer 3

Looks like in Rails 3 you would want to use the where:

Topic.where("name ILIKE ?", "%apple%")

Sprightly

Not sure if this would work - is "~=" valid sql? Or are you thinking of the ruby pattern matcher "=~". If so it wouldn't work here as it isn't sql – Ridge

Yeah I noticed that in the post I referenced and just took it at face value... I will change to LIKE and be safe. Thanks! – Sprightly

The ~= operator works for PostgreSQL -- it's a regex match. But yeah, Scott's answer should work. You might want to use ILIKE, which gives a case-insensitive search. – Kneehigh

Answer 4

Don't put string directly like that. Its called SQL injection. You should instead use .where:

Topic.where("name like '?%' ", params[:name])

Folio

Is that supposed to be "name like '?%' " or "name like '%?%' " – Slosh

This will not work exactly as noted because Rails will automatically quote the string. You'll need to tack on the % before the substitution, for example Topic.where("name like ?", "#{params[:name]}%"). – Kneehigh

Beautiful answer. 😎 – Genovera

Answer 5

Try

Topic.where("name like ?",'%apple%')
Topic.find(:all, :conditions => ["name like ?","%apple%"])

Belldas

SQL injection in first example, should be Topic.where("name like ?","%apple%"). Second one's OK. – Kneehigh

You can also do this in Rails 4+: Topic.find_by("name like ?", "%apple%") – Mittiemittimus