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

gemmysql2ruby-on-rails-3

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?

Best Answer

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

Related Topic