Can I capture the SQL generated from a query?

rubyonrails-talk
1

I want to get the SQL string that was used in a AR query...I know that I can just look in the log, but I want to do it programmatically.

ActiveRecord.sql_from { User.find(:all) } => "SELECT * FROM users"

kinda thing. Is it possible to do that?

The reason behind this is that I've got a SELECT query, and I'd like to generate a SELECT COUNT query from it. So if there's a better way than capturing the SQL and inserting the COUNT statement, I'd love to know it.

Pat

2

I don't know how to get the SQL out of a query, but I did write a plugin that'll let me generate a count query from a regular find.

3

I want to get the SQL string that was used in a AR query...I know that I can just look in the log, but I want to do it programmatically.

After a whole bunch of cascading methods, a Post.find(1) is converted into select_all("SELECT * FROM posts WHERE id=1") from the abstract class.

Each adapter is overriding execute method wich invokes @connection.query(sql) and @logger.

ActiveRecord::Base.connection.raw_connection.query("SELECT * FROM personnes WHERE id=1").class

=> Mysql::Result http://railsmanual.com/class/Mysql%3A%3AResult

@logger per default writes in a file.

So instead of writing in a file, you can ask @logger to ouput the result.

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

=> #<Logger:0xb726e604 @formatter=nil, @level=0, @default_formatter=#<Logger::Formatter:0xb726e5dc @datetime_format=nil>, @progname=nil, @logdev=#<Logger::LogDevice:0xb726e5b4 @filename=nil, @mutex=#<Logger::LogDevice::LogDeviceMutex:0xb726e58c @mon_entering_queue=, @mon_count=0, @mon_owner=nil, @mon_waiting_queue=>, @dev=#<IO:0xb7cfc030>, @shift_size=nil, @shift_age=nil>>

ActiveRecord::Base.clear_active_connections!

=> {}

Post.find(1)

  SQL (0.000089) SET SQL_AUTO_IS_NULL=0   Post Load (0.000174) SELECT * FROM posts WHERE (posts.id = 1) => #<Post:0xb72537c8 @attributes={../..}>

We can geek the logger. That's cool.

Look at the initialize method of Logger::LogDevice::LogDeviceMutex. It asks:

  if log.respond_to?(:write) and log.respond_to?(:close)     ../..   end

But

@my_logger = ''

=> ""

@my_logger.respond_to?(:write)

=> false

@my_logger.class

=> String

So you could add a write method in the String class:

class String def write(args)    self.replace(args) end end

=> nil

@my_logger.respond_to?(:write)

=> true

@my_logger.write('kikoo')

=> "kikoo"

You need actually also a close method:

class String def close(args=self)    self.delete!(args) end end

=> nil

ActiveRecord::Base.logger = Logger.new(@my_logguer)

=> ../..

ActiveRecord::Base.clear_active_connections!

=> {}

Post.find(1)

=> #<Post:0xb725ab68 @attributes={../..}>

@my_logger

=> " \e[4;36;1mPost Load (0.000667)\e[0m \e[0;1mSELECT * FROM posts WHERE (posts.id = 2) \e[0m\n"

Colorization may suck:

ActiveRecord::Base.colorize_logging = false

=> false

Post.find(1)

=> #<Post:0xb725ab68 @attributes={../..}>

@my_logger

=> "Post Load (0.000329) SELECT * FROM posts WHERE (posts.id = 2) \n"

Anyone has an easier idea?

Just my $0.02

4

Not quite. Normally they'd be fine, but what I'm trying to do is take some query and convert it to a count query programatically.

Pat

5

So if there's a better way than capturing the SQL and inserting the COUNT statement, I'd love to know it.

Pat

Will count or count_by_sql work? count: http://api.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html#M000951

count_by_sql: ActiveRecord::Base

John Miller

Not quite. Normally they'd be fine, but what I'm trying to do is take some query and convert it to a count query programatically.

http://agilewebdevelopment.com/plugins/count_from_query

......

count_from_query gives you the ability to generate a COUNT query from a standard Rails find.

For example, if you have the query

User.find :all

it would be trivial to get a count:

User.count

however, if you have a more specific finder method, such as

class Company < ActiveRecord::Base def complete_videos Video.find :all, :conditions => "company_id=#{id} AND status='complete'", :order => "created_at DESC" end

Getting the count isn't quite as easy. You could just call #size on the returned array, but it's wasteful if you don't actually need the records. You could write a complete_videos_count method, but it doesn't feel very DRY to have two methods every time you want to do a count query as well.

With count_from_query, it's cake

videos_count = ActiveRecord::Base.count_from_query { my_company.complete_videos }

You can wrap any AR find call in count_from_query to have it be converted into a count query.

Association proxies work the same way. We could change the complete_videos definition to be

class Company < ActiveRecord::Base def complete_videos videos.find :all, :conditions => "status='complete'", :order => "created_at DESC" end

and get the same result.

6

Very cool, the author sure is one clever dude :wink:

Pat

7

Confusingly that is you right Pat?

8

Yeah that's me. I asked the question yesterday, worked on some other stuff, and then wrote that plugin when I didn't get any responses. I just thought it was funny that someone answered my question with a plugin I wrote, that's all.

Pat