Search for one specific id in ruby using where but returns multiple different ones

Question

I try to return the courses with specific subject id in ruby using where. In the console it gives the correct id I want but it return many courses with wrong id. Can anyone tell me what is wrong with my code? Thank you!

def display
subject_id = search_params[:subject]
puts subject_id
@courses = Course.where("subject_id", subject_id)
puts @courses.inspect
@courses = @courses.where("name LIKE ?", search_params[:course])
puts "******"
puts @courses.inspect
puts "******"
end

This is what appears in the console

Started POST "/search" for 127.0.0.1 at 2018-10-20 18:22:41 -0400
Processing by UsersController#display as JS
Parameters: {"utf8"=>"✓", "subject"=>"1161-850", "course"=>"",      "commit"=>"Search"}
Unpermitted parameters: :utf8, :commit
1161-850
Course Load (0.2ms)  SELECT  "courses".* FROM "courses" WHERE  (subject_id) LIMIT ?  [["LIMIT", 11]]

This is what displayed in console

Unpermitted parameters: :utf8, :commit
******
Course Load (0.4ms)  SELECT  "courses".* FROM "courses" WHERE  (subject_id) AND (name LIKE '') LIMIT ?  [["LIMIT", 11]]
#<ActiveRecord::Relation []>
******
Rendering users/display.html.erb within layouts/application
Course Load (0.4ms)  SELECT "courses".* FROM "courses" WHERE (subject_id) AND (name LIKE '')
Rendered users/display.html.erb within layouts/application (1.8ms)
Rendered layouts/_shim.html.erb (0.5ms)
User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
Rendered layouts/_header.html.erb (2.0ms)
Rendered layouts/_footer.html.erb (0.9ms)

The one I guess in your CoursesController. You have two unpermitted parameters, because you're sending your data without the proper format. — Sebastián Palma, Oct 20 '18 at 23:27
Take a look at the generated SQL: `SELECT "courses".* FROM "courses" WHERE (subject_id) LIMIT ? [["LIMIT", 11]]`, you're not limiting based on `subject_id` properly — Simple Lime, Oct 21 '18 at 00:39
There is no `where` in ruby. Please pay more attention to the tags. — Aleksei Matiushkin, Oct 21 '18 at 04:41

spickermann · Answer 1 · 2018-10-21T16:50:10.453

Course.where("subject_id", subject_id) doesn't do what you expect. It generates a condition with the string "subject_id" and would pass the value of subject_id as an argument to that condition is there was a ? in the condition.

Just use write this instead

Course.where(subject_id: subject_id)

I suggest reading about the ActiveRecord Query interface in the Rails Guides.

Furthermore the LIKE query a few lines later will also not work. You might want to look the this question about generating like queries in ActiveRecord.

score 0 · Answer 2 · answered Oct 21 '18 at 13:08

If you want to find courses with specific subject_id and course name then you can do it in one query:

def display
   subject_id = search_params[:subject]
   course = search_params[:course]

   @courses = Course.where("name LIKE ? AND subject_id = ? ", "%#{course}%", subject_id)
end

Search for one specific id in ruby using where but returns multiple different ones

2 Answers2