find_by_sql vs connection.select_all

I was trying to do SUM based mySQL query simliar to the following:

SELECT SUM(updated_on - created_on) AS total from signups

If I were to run this command in the mySQL console I would get a result. However, if I were to run it using the following command in Rails:

Signup.find_by_sql("SELECT SUM(updated_on - created_on) AS total from signups")

The query that is written to the log is:

SELECT SUM(updated_on - created_on) AS total from signups

Like I said before, if I take that query to the mySQL console, I get the expected result, however the result to the view is simply #

NOW, if take the query and put it in with:

Signup.connection.select_all("SELECT SUM(updated_on - created_on) AS total from signups")

I get the expected result to the view and the same SQL code is written to the log

What is the difference then in find_by_sql and connection.select_all

My initial guess is that find_by_sql is expecting a normal find(:all) based result that follows the model format whereas connection.select_all allows any sort of SQL code and gives the result required.

Any thoughts or comments appreciated.

-E

I got the same results. How I got the results out of the query was the problem. It helps to check the Rails API before posting:)

select_all and find_by_sql returns the result in the following:

"returns an array of record hashes with the column names as keys and column values as values."

So therefore in my query to get the value of the result after I set the query to the variable @sum

@sum[0]['total']

Since I am looking for just a one result line I will end up using select_one which follows a different slightly different model when returning the results:

"Returns a record hash with the column names as keys and column values as values."

When using the existing variable from before, would mean that:

@sum['total']

would give me the desired result.