randombits randombits - 1 year ago 133
MySQL Question

Rails 4 get column names in raw activerecord query

I have some code that looks like the following:

query = <<-EOF
select player_id, first_name, last_name,
max(case when site_id = 1 then salary end) fd_salary,
max(case when site_id = 2 then salary end) dd_salary,
max(case when site_id = 3 then salary end) ss_salary,
max(case when site_id = 4 then salary end) ds_salary,
max(case when site_id = 7 then salary end) dk_salary,
max(case when site_id = 8 then salary end) elite_salary
from player_salaries ps
where ps.gamedate = '2014-05-25'
and sport_id = #{$MLB_SPORT_ID}
group by player_id

salaries = PlayerSalary.connection.execute(query)

The problem is
in this case comes back as an array with values. These query is a bit complex and the names I'm using such as
and so forth aren't physical attributes in the
model. There's no way to do something like
. Is there a way to change the above in Rails 4 to get it access values by column name?

Answer Source

You could use find_by_sql for this:

find_by_sql(sql, binds = [])

Executes a custom SQL query against your database and returns all the results. The results will be returned as an array with columns requested encapsulated as attributes of the model you call this method from. If you call Product.find_by_sql then the results will be returned in a Product object with the attributes you specified in the SQL query.

So if you did:

salaries = PlayerSalary.find_by_sql(query)

then you could say things like salaries.first.fd_salary. Just don't try to use columns that weren't in your query or try to change the returned PlayerSalary objects and expect anything useful to happen.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download