using WHERE clause in Rails 3 active record query

Question

I'm new to rails and I'm trying to use the where method to retrieve a record from my data table. However, using it doesn't seem to return any results.

employee = Employee.where("first_name = ?", "bill")  #doesn't work

employee = Employee.where(:first_name => "bill")  #doesn't work

employee = Employee.find_by_first_name("bill")  #works

I'm testing the results by printing employee.first_name and like I said the first two don't return anything whereas the third one returns "bill". What's going on here?

Solution 1

The first two will return an array (all employees with that name). Employee.find_by_first_name will only return the first result -- Employee.find_all_by_first_name should return an array like the first two queries.

See if this does what you expect:

Employee.where("first_name = ?", "bill").first

(for completeness, what Employee.where actually returns is a chainable scope object that acts like an array)

Solution 2

what happens for the first two when you run employee.first_name? It looks to me like you should be getting an no method exception since Array does not have a method first_name.

Using the where clause will not automatically return the first employee model object found, it will return an ActiveRecord::Relation which will then be automatically evaluated into an array by rails when you try to access it. The where clause will give you back all employees with a first_name == "bill"

The find_by_first_name will only return a single instance of the Employee class, even if there are multiple employees with the name "bill".

If you were to try employee.first.fist_name after running the first two, I believe you would find that you get "bill" if everything in the database is correct and there is an employee with the first name of "bill".

Comments

[yiinewbie]

Also, how can I print the array (chainable scope object) that is the result of the first two statements so that I can at least inspect the contents?

[salernost]

You can call .inspect() on the object or use the shorthand and replace the 'puts' method for 'p' which will automatically call inspect on the object you are printing to screen.

[rcrogers]

Also, calling Employee.where("first_name = ?", "bill").to_a will force Rails to convert the scope into a normal array that will display normally in the console etc.