我如何看到由Ruby on Rails中的给定ActiveRecord查询生成的SQL呢

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.

我希望看到给定的ActiveRecord查询将生成的SQL语句。我认识到，在发出查询之后，我可以从日志中获取这些信息，但是我想知道是否有一个方法可以被调用和ActiveRecord查询。

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.

我希望打开irb控制台，并在末尾添加一个方法，该方法将显示该查询将生成的SQL，但不一定执行查询。

11 个解决方案

#1

When last I tried to do this there was no official way to do it. I resorted to using the function that find and its friends use to generate their queries directly. It is private API so there is a huge risk that Rails 3 will totally break it, but for debugging, it is an ok solution.

上次我尝试这么做的时候，并没有官方的方法。我使用find和它的朋友使用的函数直接生成他们的查询。它是私有的API，所以Rails 3可能会完全破坏它，但是对于调试来说，这是一个不错的解决方案。

The method is construct_finder_sql(options) (lib/active_record/base.rb:1681) you will have to use send because it is private.

该方法是built_finder_sql (options) (lib/active_record/base.rb:1681)，您将不得不使用send，因为它是私有的。

Edit: construct_finder_sql was removed in Rails 5.1.0.beta1.

Edit: built_finder_sql在Rails 5.1.0.beta1中被删除。

#2

183

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

类似于penger，但在加载了类并缓存了日志记录器之后，仍然可以在控制台中随时使用:

For Rails 2:

Rails 2:

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

For Rails 3.0.x:

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:

对于Rails >= 3.1.0，这在控制台已经默认完成了。如果噪音太大，你想关掉它，你可以这样做:

ActiveRecord::Base.logger = nil

#3

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

贴query_object。在某个地方，查看您的工作对象类型，然后查找文档。

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

例如，在Rails 3.0中，作用域使用ActiveRecord::关系，它有一个#to_sql方法。例如:

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

Then, somewhere you can do:

然后，在你可以做的地方:

puts Contact.frequently_contacted.to_sql

#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

explain方法将提供关于它将要做什么的相当详细的SQL语句

#5

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

只要使用to_sql方法，它就会输出将要运行的sql查询。它作用于一个活动记录关系。

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.

当执行find时，它将不起作用，因此需要将该id手动添加到查询中，或者使用where运行它。

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

#6

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

这是我通常在控制台生成SQL的方法

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

不可能真的为此负责，很久以前在别人的博客上发现的，不记得是谁的。

#7

Create a .irbrc file in your home directory and paste this in:

在您的主目录中创建一个.irbrc文件，并将其粘贴到:

if ENV.include?('RAILS_ENV') && !Object.const_defined?('RAILS_DEFAULT_LOGGER')
  require 'logger'
  RAILS_DEFAULT_LOGGER = Logger.new(STDOUT)
end

That will output SQL statements into your irb session as you go.

这将在您的irb会话中输出SQL语句。

EDIT: Sorry that will execute the query still, but it's closest I know of.

编辑:对不起，这将仍然执行查询，但我知道它是最近的。

EDIT: Now with arel, you can build up scopes/methods as long as the object returns ActiveRecord::Relation and call .to_sql on it and it will out put the sql that is going to be executed.

编辑:现在使用arel，只要对象返回ActiveRecord::关系和调用.to_sql，并且它将输出将要执行的sql，就可以构建范围/方法。

#8

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

尝试show_sql插件。该插件使您可以在不运行SQL的情况下打印SQL。

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

#9

You could change the connection's log method to raise an exception, preventing the query from being run.

您可以更改连接的日志方法以引发异常，以防止查询被运行。

It's a total hack, but it seems to work for me (Rails 2.2.2, MySQL):

这完全是一种黑客行为，但对我来说似乎行得通(Rails 2.2.2, MySQL):

module ActiveRecord
  module ConnectionAdapters
    class AbstractAdapter
      def log_with_raise(sql, name, &block)
        puts sql
        raise 'aborting select' if caller.any? { |l| l =~ /`select'/ }
        log_without_raise(sql, name, &block)
      end
      alias_method_chain :log, :raise
    end
  end
end

#10

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

我的典型方法是在sql中引入一个“bug”，然后您将会得到一个错误消息，它会向正常的日志记录器(和web屏幕)发送错误消息，这些消息会有sql问题。没有必要去寻找stdout的方向……

#11

In Rails 3 you can add this line to the config/environments/development.rb

在Rails 3中，您可以将这一行添加到config/environments/development.rb中

config.active_record.logger = Logger.new(STDOUT)

It will however execute the query. But half got answered :

但是，它将执行查询。但有一半人回答说:

#1