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?
If you just want to reuse the database.yml configuration, you can do this:
config = ActiveRecord::Base.configurations[RAILS_ENV].symbolize_keys
conn = Mysql2::Client.new(config)
conn.query("select * from users").each do |user|
# user should be a hash
end
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
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
results = ActiveRecord::Base.connection.select(sql)
table header
results.first.keys.each do |key|
key
end
table data
results.each do |result| %>
result.values.each do |value| %>
value
end
end
Improving dan's answer, Rails 3.2.8 won't accept RAILS_ENV.
config = ActiveRecord::Base.configurations[Rails.env].symbolize_keys
conn = Mysql2::Client.new(config)
conn.query("select * from users").each do |user|
# user should be a hash
end
