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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On 4/3/07, Pat Maddox <pergesu-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > > 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.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-plugin-easily-generate-a-count-statement-from-a-custom-finder --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Pierre-Alexandre Meyer
2007-Apr-04 06:55 UTC
Re: Can I capture the SQL generated from a query?
On Tue, Apr 03, 2007 at 06:06:26PM -0600, Pat Maddox 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 Meyer | | email : pam-1sEOgp2Wo8Qdnm+yROfE0A@public.gmane.org | `========================'' --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> So if there''s a better way > than capturing the SQL and inserting the COUNT statement, I''d love to > know it. > > PatWill count or count_by_sql work? count: http://api.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html#M000951 count_by_sql: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001002 John Miller -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 4/4/07, John Miller <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > > 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: > http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001002 > > John MillerNot 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
>>> 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: >> http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001002 >> >> 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 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 4/4/07, Philip Hallstrom <rails-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote:> > >>> 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: > >> http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001002 > >> > >> 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_queryVery cool, the author sure is one clever dude ;) Pat --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Confusingly that is you right Pat? On 4/4/07, Pat Maddox <pergesu-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > On 4/4/07, Philip Hallstrom <rails-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote: > > > > >>> 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: > > >> http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001002 > > >> > > >> 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 > > Very cool, the author sure is one clever dude ;) > > Pat > > > >-- Thanks, -Steve http://www.stevelongdo.com --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 4/4/07, Steve Longdo <steve.longdo-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > On 4/4/07, Pat Maddox <pergesu-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > On 4/4/07, Philip Hallstrom <rails-SUcgGwS4C16SUMMaM/qcSw@public.gmane.org> wrote: > > > > > > >>> 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: > > > >> > http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M001002 > > > >> > > > >> 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 > > > > Very cool, the author sure is one clever dude ;) > > > > Pat > > > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---