Forum: Ruby on Rails Can I capture the SQL generated from a query?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Pat M. (Guest)
on 2007-04-04 04:07
(Received via mailing list)
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
Pat M. (Guest)
on 2007-04-04 09:23
(Received via mailing list)
On 4/3/07, Pat M. <removed_email_address@domain.invalid> wrote:
> know it.
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.

http://evang.eli.st/blog/2007/4/4/count_from_query...
Pierre-Alexandre M. (Guest)
on 2007-04-04 10:56
(Received via mailing list)
On Tue, Apr 03, 2007 at 06:06:26PM -0600, Pat M. wrote :
> 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

--
,========================.
| Pierre-Alexandre M. |
| email : removed_email_address@domain.invalid |
`========================'
John M. (Guest)
on 2007-04-04 22:06
> 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/Ca...

count_by_sql:
http://api.rubyonrails.org/classes/ActiveRecord/Ba...

John M.
Pat M. (Guest)
on 2007-04-04 23:19
(Received via mailing list)
On 4/4/07, John M. <removed_email_address@domain.invalid> wrote:
>
> count_by_sql:
> http://api.rubyonrails.org/classes/ActiveRecord/Ba...
>
> John M.

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
Philip H. (Guest)
on 2007-04-04 23:25
(Received via mailing list)
>> count_by_sql:
>> http://api.rubyonrails.org/classes/ActiveRecord/Ba...
>>
>> John M.
>
> 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
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
end

and get the same result.
Pat M. (Guest)
on 2007-04-05 00:23
(Received via mailing list)
On 4/4/07, Philip H. <removed_email_address@domain.invalid> wrote:
> >>
> >> count_by_sql:
> >> http://api.rubyonrails.org/classes/ActiveRecord/Ba...
> >>
> >> John M.
> >
> > 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

Very cool, the author sure is one clever dude ;)

Pat
Steve L. (Guest)
on 2007-04-05 04:06
(Received via mailing list)
Confusingly that is you right Pat?

On 4/4/07, Pat M. <removed_email_address@domain.invalid> wrote:
> > >>
> > > 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
>
> Very cool, the author sure is one clever dude ;)
>
> Pat
>
> >
>


--
Thanks,
-Steve
http://www.stevelongdo.com
Pat M. (Guest)
on 2007-04-05 04:42
(Received via mailing list)
On 4/4/07, Steve L. <removed_email_address@domain.invalid> wrote:
> > > >>> Pat
> > > >> John M.
> Confusingly that is you right Pat?
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
This topic is locked and can not be replied to.