find_by_sql not working on rails 3.1.3

Question

I am writing a quite complex query in Ruby on Rails 3.1.3, and I am using find_by_sql.

But I noticed a very strange behaviour, even if I use find_by_sql with very simple queries.

Here is a simple example:

Let' say that I have two models and related tables:

Model 1: Company
Table 1: companies
         fields: id, name, address

         | id | name |    address      |
         +----+------+-----------------+
         |  1 | ACME | Bond street, 56 |

and:

Model 2: Employee
Table 2: employees
         fields:  id, name, age

         | id | name | age |
         +----+------+-----+
         |  1 | Fred |  56 |
         |  2 | Adam |  27 |

Here is what happens; if I write:

Company.find_by_sql("SELECT * FROM `employees`")

I get:

Company Load (0.3ms)  SELECT * from `employees`
=> [#<Company id: 1, name: "Fred">, #<Company id: 2, name: "Adam">]

I only get the fields of employees whose names match the ones in companies (i.e., the field age is missing)! No @attributes at all.

Is it a bug? Can anyone please help me?

Answer 1

The console uses pretty printing to output data from any instances returned by the query. Pretty printing is implemented automatically in the class by ActiveRecord according to the columns associated with that particular model, and won't therefore display attributes that do not belong to that particular model.

That does not mean however the attributes were not loaded into the instances. Pretty printing is just not showing them, but they are still there.

So if you just do:

Company.find_by_sql("SELECT * from employees").first.age

You should still get 56 according to your example.

Answer 2

try:

Employee.find_by_sql("SELECT * FROM `employees`")

Answer 3

If you're selecting from the employees table you will want to use the Employee model.

Try this instead

Employee.find_by_sql("SELECT id, name, age FROM `employees`")