如何将此代码转换为原始sql并在rails中使用?因为当我在heroku中部署此代码时，有一个请求超时错误。我认为这将更快，如果我使用原始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)
当前回答
你也可以混合原始SQL和ActiveRecord条件，例如，如果你想在条件中调用一个函数:
my_instances = MyModel.where.not(attribute_a: nil) \
.where('crc32(attribute_b) = ?', slot) \
.select(:id)
其他回答
你可以这样做:
sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)
Records_array将是SQL查询在数组中的结果，您可以遍历该数组。
您可以使用ActiveRecord执行原始查询。我建议使用SQL块
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)
我知道这很旧…但我今天遇到了同样的问题，我找到了一个解决办法:
Model.find_by_sql
如果你想实例化结果:
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
如果你只想要一个值的哈希值:
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"}
]
结果对象:
Select_all返回一个结果对象。你可以用它来变魔术。
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
来源:
ActiveRecord -查找by SQL。 Ruby on Rails -活动记录结果 。
你也可以混合原始SQL和ActiveRecord条件，例如，如果你想在条件中调用一个函数:
my_instances = MyModel.where.not(attribute_a: nil) \
.where('crc32(attribute_b) = ?', slot) \
.select(:id)
我想使用ActiveRecord类的exec_query，因为它返回查询转换为对象的映射，所以当主题是Raw SQL时，对对象进行迭代是非常实用和高效的。
例子:
values = ActiveRecord::Base.connection.exec_query("select * from clients")
p values
并返回这个完整的查询:
[{"id": 1, "name": "user 1"}, {"id": 2, "name": "user 2"}, {"id": 3, "name": "user 3"}]
只获取值的列表
p values.rows
[[1, "user 1"], [2, "user 2"], [3, "user 3"]]
只获取字段列
p values.columns
["id", "name"]