How can I see the SQL that will be generated by a given ActiveRecord query in Ruby on Rails

Solution 1:

Similar to penger's, but works anytime in the console even after classes have been loaded and the logger has been cached:

For Rails 2:

ActiveRecord::Base.connection.instance_variable_set :@logger, Logger.new(STDOUT)

For Rails 3.0.x:

ActiveRecord::Base.logger = Logger.new(STDOUT)

For Rails >= 3.1.0 this is already done by default in consoles. In case it's too noisy and you want to turn it off you can do:

ActiveRecord::Base.logger = nil

Solution 2:

Stick a puts query_object.class somewhere to see what type of object your working with, then lookup the docs.

For example, in Rails 3.0, scopes use ActiveRecord::Relation which has a #to_sql method. For example:

class Contact < ActiveRecord::Base
  scope :frequently_contacted, where('messages_count > 10000')
end

Then, somewhere you can do:

puts Contact.frequently_contacted.to_sql

Solution 3:

just use to_sql method and it'll output the sql query that will be run. it works on an active record relation.

irb(main):033:0> User.limit(10).where(:username => 'banana').to_sql
=> "SELECT  "users".* FROM "users"  WHERE "users"."username" = 'banana'
LIMIT 10"

when doing find, it won't work, so you'll need to add that id manually to the query or run it using where.

irb(main):037:0* User.where(id: 1).to_sql
=> "SELECT "users".* FROM "users"  WHERE "users"."id" = 1"

Solution 4:

This may be an old question but I use:

SampleModel.find(:all,
                 :select => "DISTINCT(*)",
                 :conditions => ["`date` > #{self.date}"], 
                 :limit=> 1, 
                 :order => '`date`',
                 :group => "`date`"
                 ).explain

The explain method will give quite a detailed SQL statement on what its going to do