[sql] Rails raw SQL example

How can I convert this code to raw sql and use in rails? Because When I deploy this code in heroku,there is a request timeout error.I think this will be faster if I use raw sql.

@payments = PaymentDetail.joins(:project).order('payment_details.created_at desc')
@payment_errors = PaymentError.joins(:project).order('payment_errors.created_at desc')

@all_payments = (@payments + @payment_errors)

I want to work with exec_query of the ActiveRecord class, because it returns the mapping of the query transforming into object, so it gets very practical and productive to iterate with the objects when the subject is Raw SQL.

Example:

values = ActiveRecord::Base.connection.exec_query("select * from clients")
p values

and return this complete query:

[{"id": 1, "name": "user 1"}, {"id": 2, "name": "user 2"}, {"id": 3, "name": "user 3"}]

To get only list of values

p values.rows

[[1, "user 1"], [2, "user 2"], [3, "user 3"]]

To get only fields columns

p values.columns

["id", "name"]

You can also mix raw SQL with ActiveRecord conditions, for example if you want to call a function in a condition:

my_instances = MyModel.where.not(attribute_a: nil) \
  .where('crc32(attribute_b) = ?', slot) \
  .select(:id)

You can do direct SQL to have a single query for both tables. I'll provide a sanitized query example to hopefully keep people from putting variables directly into the string itself (SQL injection danger), even though this example didn't specify the need for it:

@results = []
ActiveRecord::Base.connection.select_all(
  ActiveRecord::Base.send(:sanitize_sql_array, 
   ["... your SQL query goes here and ?, ?, ? are replaced...;", a, b, c])
).each do |record|
  # instead of an array of hashes, you could put in a custom object with attributes
  @results << {col_a_name: record["col_a_name"], col_b_name: record["col_b_name"], ...}
end

Edit: as Huy said, a simple way is ActiveRecord::Base.connection.execute("..."). Another way is ActiveRecord::Base.connection.exec_query('...').rows. And you can use native prepared statements, e.g. if using postgres, prepared statement can be done with raw_connection, prepare, and exec_prepared as described in https://stackoverflow.com/a/13806512/178651

You can also put raw SQL fragments into ActiveRecord relational queries: http://guides.rubyonrails.org/active_record_querying.html and in associations, scopes, etc. You could probably construct the same SQL with ActiveRecord relational queries and can do cool things with ARel as Ernie mentions in http://erniemiller.org/2010/03/28/advanced-activerecord-3-queries-with-arel/. And, of course there are other ORMs, gems, etc.

If this is going to be used a lot and adding indices won't cause other performance/resource issues, consider adding an index in the DB for payment_details.created_at and for payment_errors.created_at.

If lots of records and not all records need to show up at once, consider using pagination:

• https://www.ruby-toolbox.com/categories/pagination
• https://github.com/mislav/will_paginate

If you need to paginate, consider creating a view in the DB first called payment_records which combines the payment_details and payment_errors tables, then have a model for the view (which will be read-only). Some DBs support materialized views, which might be a good idea for performance.

Also consider hardware or VM specs on Rails server and DB server, config, disk space, network speed/latency/etc., proximity, etc. And consider putting DB on different server/VM than the Rails app if you haven't, etc.

You can execute raw query using ActiveRecord. And I will suggest to go with SQL block

query = <<-SQL 
  SELECT * 
  FROM payment_details
  INNER JOIN projects 
          ON projects.id = payment_details.project_id
  ORDER BY payment_details.created_at DESC
SQL

result = ActiveRecord::Base.connection.execute(query)

I know this is old... But I was having the same problem today and found a solution:

Model.find_by_sql

If you want to instantiate the results:

Client.find_by_sql("
  SELECT * FROM clients
  INNER JOIN orders ON clients.id = orders.client_id
  ORDER BY clients.created_at desc
")
# => [<Client id: 1, first_name: "Lucas" >, <Client id: 2, first_name: "Jan">...]

Model.connection.select_all('sql').to_hash

If you just want a hash of values:

Client.connection.select_all("SELECT first_name, created_at FROM clients
   WHERE id = '1'").to_hash
# => [
  {"first_name"=>"Rafael", "created_at"=>"2012-11-10 23:23:45.281189"},
  {"first_name"=>"Eileen", "created_at"=>"2013-12-09 11:22:35.221282"}
]

Result object:

select_all returns a result object. You can do magic things with it.

result = Post.connection.select_all('SELECT id, title, body FROM posts')
# Get the column names of the result:
result.columns
# => ["id", "title", "body"]

# Get the record values of the result:
result.rows
# => [[1, "title_1", "body_1"],
      [2, "title_2", "body_2"],
      ...
     ]

# Get an array of hashes representing the result (column => value):
result.to_hash
# => [{"id" => 1, "title" => "title_1", "body" => "body_1"},
      {"id" => 2, "title" => "title_2", "body" => "body_2"},
      ...
     ]

# ActiveRecord::Result also includes Enumerable.
result.each do |row|
  puts row['title'] + " " + row['body']
end

Sources:

1. ActiveRecord - Findinig by SQL.
2. Ruby on Rails - Active Record Result .