12

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?

nickgrim
  • 5,387
  • 1
  • 22
  • 28
pduey
  • 3,706
  • 2
  • 23
  • 31

5 Answers5

46

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

Rahul Jha
  • 589
  • 3
  • 6
26

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

dsjoerg
  • 652
  • 1
  • 7
  • 11
6

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
dan
  • 43,914
  • 47
  • 153
  • 254
  • That is an improvement over how I did do it, so barring anything "just working", I'll use this. Thank you. – pduey Apr 25 '11 at 15:43
  • dan can you help me with my query please? http://stackoverflow.com/questions/22997103/find-by-sql-is-not-working-with-prepare-statement – Carlos Morales Apr 11 '14 at 02:24
2
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
Exsemt
  • 1,048
  • 10
  • 22
0

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
hammady
  • 969
  • 1
  • 13
  • 22