Find_by_sql without a model? how to do this?

Rails
1

Hi,
I’d like to do a “find_by_sql” without a model (e.g. .find_by_sql("…")) as the results I get back are a once off
special,
and I’m happy to handle them as an array. How do I do this?
“ActiveRecord::Base.find_by_sql(…)” does not seem to work?

Background - At the moment I’m doing within a model <model_name>,
however
within one method I need specific information (a group by) about data in
A
that will not come back in the form of the model itself. Current I do
use
<model_name>.find_by_sql(…) and get back an array which I use. This
is
fine however syntactically to me the <model_name> at the beginning is a
bit
silly as the data I get back does not go into the class structure for
<model_name>.

Thanks

2

Didn’t try that myself, but some of the methods of the connection
adapter should work:
http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#M001178

select_rows(sql, name = nil)
Returns an array of arrays containing the field values.

3

Another way would be to do:

Model.find_by_sql(…, :select =>
[:column_you_want]).map(&:column_you_want)

You can remove the map if you don’t want it to be an array.

I believe this will work. (haven’t tested it)

On May 30, 6:07 am, “Greg H.” [email protected]

4

I can’t see the “Mysql::Result” class in the Rails API doco? I was
really
after getting basic Ruby Arrays/Hashs back?

On Fri, May 30, 2008 at 10:28 PM, javier ramirez [email protected]

5

Hi,

I’d like to do a “find_by_sql” without a model (e.g. .find_by_sql("…")) as the results I get back are a once off

you can try to forget about the convenient AR wrapping and use directly
the underlying connection.

ActiveRecord::Base.connection.execute ‘select * from users’

Will return an object representing the resultset. In the case of Mysql
you will get a Mysql::Result and you can use the fetch_row method to
move along the records retrieved.

regards,

javier ramírez

6

On May 30, 10:57 pm, “Greg H.” [email protected]
wrote:

I can’t see the “Mysql::Result” class in the Rails API doco? I was really
after getting basic Ruby Arrays/Hashs back?

ActiveRecord::Base.connection.select_all returns an array of hashes.
(sometimes select_values and some of the other select_xxx methods are
also useful).

Fred

7

excellent, thanks

On Sat, May 31, 2008 at 8:11 AM, Frederick C. <

8

goood

+1 point, I learnt more one new thing.

thanks
Reinhart

9

On Jun 1, 7:01 am, “Greg H.” [email protected]
wrote:

actually with “ActiveRecord::Base.connection.select_all” it doesn’t seem to
allow use of “?” to populate variables - does this mean one has to manually
“sanitize” the SQL for security, and if so which calls would you normally
apply over the sql string you plan on using for
“ActiveRecord::Base.connection.select_all”?
thanks

You can use sanitize_sql to expand some conditions (it’s a protected
method, so you either need to be calling this from a cass method of an
ActiveRecord subclass or use send), or you can use connection.quote to
quote a parameter.

Fred

10

actually with “ActiveRecord::Base.connection.select_all” it doesn’t seem
to
allow use of “?” to populate variables - does this mean one has to
manually
“sanitize” the SQL for security, and if so which calls would you
normally
apply over the sql string you plan on using for
“ActiveRecord::Base.connection.select_all”?
thanks

On Sat, May 31, 2008 at 8:11 AM, Frederick C. <