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


4 Answers

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
Question

I would like to see the SQL statement that a given ActiveRecord Query will generate. I recognize I can get this information from the log after the query has been issued, but I'm wondering if there is a method that can be called on and ActiveRecord Query.

For example:

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

I would like to open the irb console and tack a method on the end that would show the SQL that this query will generate, but not necessarily execute the query.




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




Try the show_sql plugin. The plugin enables you to print the SQL without running it

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



This is what I usually do to get SQL generated in console

-> script/console
Loading development environment (Rails 2.1.2)
>> ActiveRecord::Base.logger = Logger.new STDOUT
>> Event.first

You have to do this when you first start the console, if you do this after you have typed some code, it doesn't seem to work

Can't really take credit for this, found it long time ago from someone's blog and can't remember whose it is.




My typical way to see what sql it uses is to introduce a "bug" in the sql, then you'll get an error messages spit out to the normal logger (and web screen) that has the sql in question. No need to find where stdout is going...




Related