CODEWITHSUNDEEP
ruby-on-railsactiverecord
Dan Tappin
Dan Tappin

Reputation: 3032

How to output the results of a raw SQL Active Record select query?

I am testing some Active Record call in the console. I often have raw SQL selects like this:

Model.joins(:other_model).where(some_column:foo).select("other_model.column AS col1, model.column AS col2")

Which will just result this:

[#<Model:0x00007fa45f25b3d0 id: nil>,
 #<Model:0x00007fa45f25b1c8 id: nil>,
 #<Model:0x00007fa45f25afc0 id: nil>,
 #<Model:0x00007fa45f25acc8 id: nil>,
 #<Model:0x00007fa45f25aa48 id: nil>,
 #<Model:0x00007fa45f25a6d8 id: nil>]

Can I have the console output the values from the "other_model.column"? I swear I saw a trick for this before but can't seem to find this anywhere.

Upvotes: 2

Views: 3409

Answers (2)

Chris Heald
Chris Heald

Reputation: 62638

Fundamentally, the issue is that ActiveRecord is going to attempt to cast the resulting rows as Models. However, the attributes are still loaded! They're just not in the string representation of the model, since they don't exist as columns on the table.

Given a relation:

relation = Model.joins(:other_model).where(some_column:foo)
                .select("other_model.column AS col1, model.column AS col2")

You can easily see the computed columns by just mapping out #attributes:

relation.map(&:attributes)

You will still end up with id fields in attributes regardless, but you can easily ignore them, or further process the mapped attributes to remove them if needed.

For example:

> Account.where("id < 1000")
         .select("id * 1024 as bigid")
         .limit(10)
         .map(&:attributes)
=> [{"id"=>nil, "bigid"=>819200},
    {"id"=>nil, "bigid"=>820224},
    {"id"=>nil, "bigid"=>822272}]

Another alternative would be to skip the ActiveRecord loading, and use #to_sql to just execute a raw query. In this case, I have a Postgres connection, so I'm using #exec_query; it may vary based on your RDBMS:

Given our relation:

> ActiveRecord::Base.connection.exec_query(relation.to_sql).to_hash
=> [{"bigid"=>"819200"}, {"bigid"=>"820224"}, {"bigid"=>"822272"}]

Upvotes: 3

Abdulaziz
Abdulaziz

Reputation: 2211

You can use the #pluck method to get an array of column values, for example: 

Model.joins(:other_model).where(some_column:foo).pluck("other_model.column")

Upvotes: 1

Related Questions