Rails .where query not working

Question

I have a table called Toys that has the columns toy_id, company_id, store_id, name and description

I am trying to do a query on the table like the following: toys = @company.toys.where("store_id = ? AND (name LIKE ? OR description LIKE ?)", params[:store_id], params[:query], params[:query]

The idea is if the user goes to the following url: https://myapp.com/api/toys/search?store_id=8&query="robot" that it will return all the toys for the store denoted by store_id that have a name or description LIKE robot. Each time it returns empty JSON. Not exactly sure why? Any help or guidance would be appreciated.

try this `toys = @company.toys.where("store_id = ? AND (name LIKE ? OR description LIKE ?)", params[:store_id], "%#{params[:query]}%", "%#{params[:query]}%"`. — Pardeep Dhingra, Jan 12 '16 at 14:19
@axlj he is receiving param query not name or description. https://myapp.com/api/toys/search?store_id=8&query="robot" — Pardeep Dhingra, Jan 12 '16 at 14:24
If you look at the url, I want ```:query``` to be used to match against the ```toy.description``` and ```toy.name```. — Zack Herbert, Jan 12 '16 at 14:24
its not vulnerable to sql injections http://stackoverflow.com/questions/19105706/rails-4-like-query-activerecord-adds-quotes — Pardeep Dhingra, Jan 12 '16 at 14:25
@charlysisto I am trying to figure that out. I will reply with an answer shortly — Zack Herbert, Jan 12 '16 at 14:30
@PardeepDhingra Your answer was correct! I was still passing in query like ```...&query="robot"``` when it should be ```...&query=robot``` since it is now being treated like a string in the api. Thank you for your help everyone. — Zack Herbert, Jan 12 '16 at 14:44

score 1 · Answer 1 · answered Jan 12 '16 at 14:35

1

Using Arel style:

companies = Company.arel_table

toys = @company.where(store_id: params[:store_id]).where(companies[:name].matches(params[:query]).or(companies[:description].matches(params[:query])))

Or even:

companies = Company.arel_table

search_query = companies[:name].matches(params[:query]).or(companies[:description].matches(params[:query]))

toys = @company.where(store_id: params[:store_id]).where(search_query)

answered Jan 12 '16 at 14:35

lcguida

3,787
2
33
56

I will give this a try – Zack Herbert Jan 12 '16 at 14:37

score 1 · Answer 2 · answered Jan 12 '16 at 15:52

As You 'have a table called Toys', You could try something like this:

@toys = Toy.where("company_id = ? AND store_id = ? AND (name LIKE ? OR description LIKE ?)", params[:company_id], params[:store_id], params[:query], params[:query]

I hope it will help You!

score 0 · Accepted Answer · edited Jan 15 '16 at 07:05

0

Refer to the first comment in the original question by @PardeepDhingra

toys = @company.toys.where("store_id = ? AND (name LIKE ? OR description LIKE ?)", params[:store_id], "%#{params[:query]}%", "%#{params[:query]}%"

edited Jan 15 '16 at 07:05

AJ X.

2,729
1
23
33

answered Jan 12 '16 at 14:49

Zack Herbert

942
1
16
39

Rails .where query not working

3 Answers3