How can I get SQL statement created by ActiveRecord#find without actually executing it?

I am using will_paginate with 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.

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 SELE开发者_如何学运维CT COUNT(*) FROM ... manually, in order to get the number of records.

Any ideas how to do it?

Edit: I am using Rails 2.3.x

For Rails 3:

Check out the ActiveRecord::Relation docs at the Rails 3 docs.

# 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

It seems thatm in Rails 2.x, a private method called ActiveRecord::Base#construct_finder_sql could be used, I need to test it more and see whether it will work for me:

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

I know the question asks "without executing it", but the #explain method is super useful and should at least be mentioned here. It is extremely useful for debugging slow queries.

Note: It does execute the query though.

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)

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.

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.

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)

For Rails 4 and above use to_sql as explained in the above.

Original answer

Sometime ago, I used a plugin called sql_display for this.

>> 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\""