randombits randombits - 20 days ago 5
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
EOF

salaries = PlayerSalary.connection.execute(query)


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

Answer

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.