Rails 原始 SQL 示例

Rails raw SQL example

提问by Johnny Cash

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.

如何将此代码转换为原始 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)

回答by Huy

You can do this:

你可以这样做：

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

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

records_array然后将是您可以迭代的数组中 sql 查询的结果。

回答by Jo?o Paulo Motta

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_allreturns a resultobject. You can do magic things with it.

select_all返回一个result对象。你可以用它做神奇的事情。

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 .

1. ActiveRecord - 通过 SQL 查找。
2. Ruby on Rails - 活动记录结果 。

回答by Deepak Mahakale

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

您可以使用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)

回答by Gary S. Weaver

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:

您可以使用直接 SQL 对两个表进行单个查询。我将提供一个经过清理的查询示例，以防止人们将变量直接放入字符串本身（SQL 注入危险），即使此示例没有指定对它的需求：

@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

编辑：正如 Huy 所说，一个简单的方法是ActiveRecord::Base.connection.execute("..."). 另一种方式是ActiveRecord::Base.connection.exec_query('...').rows。并且您可以使用本机准备好的语句，例如，如果使用 postgres，则可以使用 raw_connection、prepare 和 exec_prepared 来完成准备好的语句，如https://stackoverflow.com/a/13806512/178651 中所述

You can also put raw SQL fragments into ActiveRecord relational queries: http://guides.rubyonrails.org/active_record_querying.htmland 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.

您还可以将原始 SQL 片段放入 ActiveRecord 关系查询：http: //guides.rubyonrails.org/active_record_querying.html以及关联、范围等。您可能可以使用 ActiveRecord 关系查询构建相同的 SQL，并且可以使用Ernie 在http://erniemiller.org/2010/03/28/advanced-activerecord-3-queries-with-arel/ 中提到的 ARel 。而且，当然还有其他 ORM、宝石等。

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.

如果这将被大量使用并且添加索引不会导致其他性能/资源问题，请考虑在数据库中为 payment_details.created_at 和 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

• 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.

如果您需要分页，请考虑首先在数据库中创建一个名为 payment_records 的视图，它结合了 payment_details 和 payment_errors 表，然后有一个视图模型（将是只读的）。一些 DB 支持物化视图，这对于性能来说可能是一个好主意。

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.

还要考虑 Rails 服务器和 DB 服务器上的硬件或 VM 规格、配置、磁盘空间、网络速度/延迟/等、接近度等。如果还没有，请考虑将 DB 放在与 Rails 应用程序不同的服务器/VM 上等.

回答by Darlan Dieterich

I want to work with exec_queryof the ActiveRecordclass, 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.

我想工作，exec_query对中ActiveRecord类，因为它返回的查询转变为对象的映射，所以它会非常实用，富有成效的迭代与对象时，主题是原始的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"]

回答by tsauerwein

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

您还可以将原始 SQL 与 ActiveRecord 条件混合使用，例如，如果您想在条件中调用函数：

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