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)

Solution 1:

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.

Solution 2:

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 .

Solution 3:

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)