Ruby-on-rails 如何在不实际执行的情况下获取由 ActiveRecord#find 创建的 SQL 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3814738/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I get SQL statement created by ActiveRecord#find without actually executing it?
提问by Mladen Jablanovi?
I am using will_paginatewith some complicated queries and it is unable to correctly calculate number of total records (in order to display proper number of page links) - namely because of grouping by multiple columns.
我正在使用will_paginate一些复杂的查询，它无法正确计算总记录数（以显示适当数量的页面链接） - 即因为按多列分组。
So, I am intending to get the SELECT query which would be used to retrieve ALL records, without actually executing it, and wrap it with SELECT COUNT(*) FROM ...manually, in order to get the number of records.
因此，我打算获取用于检索所有记录的 SELECT 查询，而不实际执行它，并SELECT COUNT(*) FROM ...手动将其包装起来，以获得记录数。
Any ideas how to do it?
任何想法如何做到这一点？
Edit: I am using Rails 2.3.x
编辑：我正在使用 Rails 2.3.x
回答by yfeldblum
For Rails 3:
对于 Rails 3：
Check out the ActiveRecord::Relation docs at the Rails 3 docs.
在Rails 3 文档中查看 ActiveRecord::Relation文档。
# get the relation
rel = User.complex_scope.chained_complex_scope
# get the SQL
# this does not execute the query
sql = rel.to_sql
# find out how many records
# this executes the query behind the scenes
count = rel.size
回答by Mladen Jablanovi?
It seems thatm in Rails 2.x, a private method called ActiveRecord::Base#construct_finder_sqlcould be used, I need to test it more and see whether it will work for me:
似乎在 Rails 2.x 中，ActiveRecord::Base#construct_finder_sql可以使用调用的私有方法，我需要对其进行更多测试，看看它是否适合我：
ActionType.find(:all, :select => 'hosted, top_action_type, count(*) as count', :group => 'hosted, top_action_type').count
#=> 6
sql = ActionType.send :construct_finder_sql, :select => 'hosted, top_action_type, count(*) as count', :group => 'hosted, top_action_type'
#=> "SELECT hosted, top_action_type, count(*) as count FROM "action_types" GROUP BY hosted, top_action_type"
ActionType.count_by_sql "SELECT COUNT(*) FROM (#{sql}) a"
#=> 6
回答by daino3
I know the question asks "without executing it", but the #explainmethod is super useful and should at least be mentioned here. It is extremely useful for debugging slow queries.
我知道问题是“不执行它”，但该#explain方法非常有用，至少应该在这里提及。它对于调试慢查询非常有用。
Note: It does execute the query though.
注意：虽然它确实执行查询。
http://guides.rubyonrails.org/v3.2.8/active_record_querying.html#running-explain
http://guides.rubyonrails.org/v3.2.8/active_record_querying.html#running-explain
$ User.where("users.email LIKE '%longford%'").explain
User Load (0.6ms) SELECT `users`.* FROM `users` WHERE (users.email LIKE '%longford%')
=> EXPLAIN for: SELECT `users`.* FROM `users` WHERE (users.email LIKE '%gmail%')
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
回答by Harish Shetty
For Rails 4 and above use to_sqlas explained in the above.
对于 Rails 4 及以上版本to_sql，请按照上述说明使用。
Original answer
原答案
Sometime ago, I used a plugin called sql_displayfor this.
前段时间，我为此使用了一个名为sql_display的插件。
>> Post.sql
=> "SELECT * FROM \"posts\""
>> Post.sql(:order => "id DESC")
=> "SELECT * FROM \"posts\" ORDER id DESC"
>> Post.scoped({}).sql
=> "SELECT * FROM \"posts\""
>> Post.count_sql
=> "SELECT count(*) AS count_all FROM \"posts\""
回答by Bo Jeanes
Unfortunately in Rails 2.x this is actually quite hard. I've posted a similar question on Stack Overflow before and ended up digging deep into the source code of Rails to find a way. It just isn't architected in a way to allow this.
不幸的是，在 Rails 2.x 中，这实际上非常困难。我之前在 Stack Overflow 上发布过一个类似的问题，最终深入研究 Rails 的源代码以找到一种方法。它只是没有以允许这样做的方式构建。
What I ended up doing was running the query in a transaction that I rolled back, and for the length of the transaction setting the logger to my own StringIO object that I could read after.
我最终做的是在我回滚的事务中运行查询，并在事务的长度内将记录器设置为我自己的 StringIO 对象，我可以在之后阅读。
This is from memory but hopefully you understand it enough to adjust it if it doesn't work:
这是来自记忆，但希望你能理解它，如果它不起作用，可以调整它：
Model.transaction do
Model.logger = str = StringIO.new
Model.complex_scope.chained_complex_scope
Model.logger = ActiveRecord::Base.logger
str.rewind
str = str.read
# perform some regex on str to get the actual query
raise ActiveRecord::Rollback
end
It is ugly as hell and I never liked it (I wrapped it in a sql { Model. complex_scope.chained_complex_scope }) but it kinda worked for me (I only used it in development though, so I had some tolerance for errors)
它非常丑陋，我从不喜欢它（我将它包裹在 a 中sql { Model. complex_scope.chained_complex_scope }）但它对我有用（虽然我只在开发中使用它，所以我对错误有一定的容忍度）
