CODEWITHSUNDEEP
ruby-on-rails
Bad Programmer
Bad Programmer

Reputation: 952

How To Read Database Result in Ruby On Rails

I am sending raw SQL to a MySQL table in a RoR action:

sql1 = "SELECT COUNT(*) as cnt FROM testtabl WHERE gender= 'female' AND 
ROUND(height, 2) = ROUND('#{height}' , 2);"

sql_result1 = ActiveRecord::Base.connection.execute(sql1)

logger.debug('count is: '+sql_result1.to_s)

How can I access value 'cnt' from sql_result1?

Log shows:

count is: #<Mysql2::Result:0xb73c6c0>

Upvotes: 0

Views: 587

Answers (2)

Simran Sawhney
Simran Sawhney

Reputation: 1

So basically you want to use raw sql and get results into hash to be accessible. Try the below code where MODEL is the class name of the model. Like class name for users table could be User.

Model.connection.select_all(sql).to_hash

However, you could have used ActiveRecord where clause with inbuilt round method.

Upvotes: 0

Phlip
Phlip

Reputation: 5343

The answer to the OP's question is that a MySQL::Result behaves like an Enumeration, so you can convert it to an array of rows, then index the value of the first row:

sql_result1.to_a[0][0]

The answer to the OP's problem, however, is to simply let ActiveRecord do its thing:

Model.where(gender: 'female').where('ROUND(height, 2) = ROUND(?, 2)', height).count

The new problem is this compares floats for equality - ROUND(.., 2) notwithstanding. The fix is to compare a small range:

Model.where(gender: 'female').where('ROUND(?, 2) BETWEEN ROUND(height, 2) - 0.0000001 AND ROUND(height, 2) + 0.0000001', height).count

A less redundant database method to compare floats by range might be available...

Upvotes: 1

Related Questions