Why does Rails 3 with Mysql2 Gem ActiveRecord::Base.connection.execute(sql) return Array not Hash?

Question

I'm in the process of upgrading an application to Rails 3. I've decided to go with the mysql2 gem. There's some legacy code in the app that makes calls like:

results = ActiveRecord::Base.connection.execute(sql)

In the 2.3.x version, it used

results.each_hash do |row|
...

But with gem mysql2, results is type Mysql2::Result, which has only an each method. Checked the docs and they specify results should be a hash keyed on field name. Great!

But in fact, it is an Array, not a Hash.

When I use the rails console and instantiate my own Mysql2::Client and run the query there, the results are a Hash, which is what I want.

In the rails application, I think it's better to use ActiveRecord::Base.connection, since it's been instantiated with options from database.yml.

Note, unfortunately the result does not map to a model, so I can't use that.

What I've done for now is, for example:

result = ActiveRecord::Base.connection.execute(sql)
field_index = result.fields.index("field")
result.each do |row|
  row[field_index]
end

Which is ugly as sin.

Does anyone how I can get it to return a Hash instead of Array?

Answer 1

I faced a similar issue a while back and found this to work:

result = ActiveRecord::Base.connection.execute(sql) 
result.each(:as => :hash) do |row| 
   row["field"] 
end

edit: you could also use the select_all method of the connection object that returns a hash

Answer 2

instead of

result = ActiveRecord::Base.connection.execute(sql)

do

results = ActiveRecord::Base.connection.exec_query(sql)

And that'll do exactly what you want. In particular,

results.first

will be a hash, and so on.

Thanks to @_fx for figuring this out!

For more, see http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Mysql2Adapter.html#method-i-exec_query