find_by_sql without a model? how to do this?

rubyonrails-talk
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

Hi,

I'd like to do a "find_by_sql" without a model (e.g. <model >.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

4

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)

5

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

6

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

8

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

9

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