Getting Typed Results from Activerecord Raw SQL

Getting typed results from ActiveRecord raw SQL

This works for me in rails 5

results = ActiveRecord::Base.connection.select_all(sql)
results.rows.map{ |row| Hash[results.columns.zip(row)] }

Gives nice results

[{"person1"=>563, "person2"=>564, "count"=>1},
 {"person1"=>563, "person2"=>566, "count"=>5},
 {"person1"=>565, "person2"=>566, "count"=>1}]

Rails: Run raw sql query returning extra info and build models

Are you trying to do something like this:

ActiveRecord::Base.connection.execute("sql here").map do |hash|
  new_info = harvest_info(hash)
  Comment.new(hash.merge(new_info)) if some_requirement?
end

ActiveRecord execute raw transaction typecasting?

I think you are referring to ORM (Object relational mapping)

First of all, connection.execute will return a Mysql adapter where you can just iterate over the rows

You cant convert array of strings(the result you have) to ActiveRecord objects just like that ( I guess this is what you referred as typecasting)

What you can do is use find_by_sql.

Ex:

Blog.find_by_sql("select * from blog")
# => [#<Blog id: 1, name: "first blog", description: nil, record_status_id: 1>]

Using this method you can get ActiveRecord Objects fro raw SQL

Rails raw SQL example

You can do this:

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

records_array would then be the result of your sql query in an array which you can iterate through.

Return hash from raw sql query

There are different ways you can execute a raw query in Rails (with ActiveRecord):

query = <<-SQL
  SELECT TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH') AS formatted_date,
         SUM(price * quantity) AS total
  FROM table1s
  GROUP BY TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH')
SQL

Table1.find_by_sql(query).to_h { |table| [table.formatted_date, table.total] }
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

ActiveRecord::Base.connection.execute(query).values.to_h
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

ActiveRecord::Base.connection.exec_query(query).rows.to_h
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

You could give them a try and see how they perform. However, I must mention that the ActiveRecord version is much shorter, clear and easy to get:

Table1.group("TO_CHAR(date::timestamptz, 'YYYY-MM-DD HH')").sum('price*quantity')
# SELECT SUM(price*quantity) AS sum_priceallquantity, TO_CHAR(date, 'YYYY-MM-DD HH') AS to_char_date_yyyy_mm_dd_hh FROM "table1s" GROUP BY TO_CHAR(date, 'YYYY-MM-DD HH')
# {"2020-01-01 12"=>30, "2020-01-01 10"=>45}

How can I see the SQL that will be generated by a given ActiveRecord query in Ruby on Rails

When last I tried to do this there was no official way to do it. I resorted to using the function that find and its friends use to generate their queries directly. It is private API so there is a huge risk that Rails 3 will totally break it, but for debugging, it is an ok solution.

The method is construct_finder_sql(options) (lib/active_record/base.rb:1681) you will have to use send because it is private.

Edit: construct_finder_sql was removed in Rails 5.1.0.beta1.

---

Related Topics