Capturing the sql from a statement without executing it?

Hi guys,

Is there an elegant way to capture the sql that would have been executed by an ActiveRecord statement, without actually executing it? :slight_smile:

I'm imagining something like

sql = User.find(1).groups.to_sql

or perhaps

sql = ActiveRecord::Base.capture_sql { User.find(1).groups }

resulting in sql = 'SELECT * FROM groups INNER JOIN memberships.... WHERE users.id = 1'.

Any ideas?

Thanks,

Stephen

Why do you need such functionality?

I'd love to see that too. I know you can do that in the sequel orm, but not sure about active record. I'll let you know if I can find anything.

Conrad, this is why: I have a Story model which works something like 'first_object_type first_object_id did some_interesting_thing to second_object_type second_object_id'.

Users have many :groups and :contacts (other Users). Both Users and Groups have many :stories_as_first_object and :stories_as_second_object.

I want to get all the stories that might interest a User. At the moment I do this:

class User < ActiveRecord::Base

  def relevant_stories       s =       s += stories_as_first_object       s += stories_as_second_object       contacts.each { |contact|         s += contact.stories_as_first_object         s += contact.stories_as_second_object       }       groups.each { |group|         s += group.stories_as_first_object         s += group.stories_as_second_object       }       s.uniq.sort_by { |story| story.created_at }.reverse   end

end

...which works fine but issues a lot of queries. I would prefer to do something like this:

class User < ActiveRecord::Base

  def relevant_stories       q = ''       q << stories_as_first_object.to_sql       q << 'UNION' + stories_as_second_object       contacts.each { |contact|         q << 'UNION' + contact.stories_as_first_object.to_sql         q << 'UNION' + contact.stories_as_second_object.to_sql       }       groups.each { |group|         q << 'UNION' + group.stories_as_first_object.to_sql         q << 'UNION' + group.stories_as_second_object.to_sql       }       q << 'ORDER BY stories.created_at DESC'       connection.execute(q)   end

end

So really what I want is a nice way of performing UNIONs in AR. But I can't find anything to that effect. If you're wondering why I can't just write the full query myself, it is that I have presented a simplified example - in reality I have many more (and more complex) associations so that writing the full query would be a pain, not to mention not very DRY.

I'm grateful for any insights.

A while ago, I had a similar need. I wanted to get the sql back but wanted it to execute as well. I came up with this:

# get the query back from ActiveRecord class ActiveRecord::Base   def self.find_by_and_return_sql(sql)     sanitized_sql = sanitize_sql(sql)     return find_by_sql(sanitized_sql), sanitized_sql   end end

This is just for find_by_sql, but you might be able to adapt it for the other finders.

Just put it in a file that gets loaded. I have a custom_extensions.rb in lib that I use for stuff like this.

Peace.

I forgot to mention that the params are the same for find_by_and_return_sql as for find_by_sql.

Phillip, I don't think this is quite the same thing... am I right that in your function I still have to provide the sql statement myself? Rather, I am looking for a way of getting the sql generated by an ActiveRecord statement..

Do something like this, but not so hackish, and that takes into
account associations and joins...

config/environment.rb:

class ActiveRecord::Base    def self.return_sql(options)      construct_finder_sql(options)    end end

Then...

$ ./script/runner "puts Occupation.return_sql(:conditions => 'id IN
(1,2,3)', :order => 'title')" SELECT * FROM "occupations" WHERE (id IN (1,2,3)) ORDER BY title

Take a look at rails/activerecord/lib/active_record/base.rb line 1427
"def find_every" and just follow it through. Also line 558 "def
construct_finder_sql(options)" is relevant.

postscript07 wrote:

Phillip, I don't think this is quite the same thing... am I right that in your function I still have to provide the sql statement myself? Rather, I am looking for a way of getting the sql generated by an ActiveRecord statement..

On Sep 17, 11:45�pm, Phillip Koebbe <rails-mailing-l...@andreas-s.net>

Well, sort of. As the name implies, I created it as a replacement for find_by_sql. When I went back to find a use case, I couldn't. So I apparently never actually used it. But if I remember correctly, it was for situations in which I created the base SQL statement myself but iterated over user input to create WHERE conditions. I didn't want to have to build custom WHERE clauses myself when Rails does a perfectly good job of it. The fact that I used it with find_by_sql is why I said that you might be able to adapt it. I was thinking that if you saw how easy it was to use the sanitize_sql method and return the result, that maybe you'd have a light bulb go off. But as I looked at the other Philip's solution, that construct_finder_sql approach looks pretty promising. If I ever need to do this again, I'm going to look into that route.

Peace.

Do something like this, but not so hackish, and that takes into
account associations and joins...

Easier said than done :wink: But thanks for the thought.

:wink:

=>

config/environment.rb:

classActiveRecord:::Base def self.return_sql(options construct_finder_sql(options) end

end

What is the meaning of the command above?

:wink: =>

config/environment.rb:

classActiveRecord:::Base def self.return_sql(options construct_finder_sql(options) end

end What is the meaning of the command above?

We're re-opening the ActiveRecord::Base class and adding a class method "return_sql" to it...

Actually Phillip, having looked at it carefully, your solution is an excellent one - it works with no modification for association collections. I settled on this:

class ActiveRecord::Base   def self.to_sql(options={})     construct_finder_sql(options)   end end

User.to_sql

=> "SELECT * FROM `users` "

User.to_sql(:conditions => {:id => 1})

=> "SELECT * FROM `users` WHERE (`users`.`id` = 1) "

User.find(1).groups.to_sql

=> "SELECT `groups`.* FROM `groups` INNER JOIN memberships ON groups.id = memberships.group_id WHERE ((`memberships`.user_id = 1)) "

A final refinement:

class ActiveRecord::Base   def self.to_sql(options={})     scope = scope(:find)     if scope and scope[:conditions].is_a?(String) and scope[:conditions].starts_with?('SELECT')       # this is actually finder_sql, not a conditions string! Rails bugfix?       scope[:conditions]     else       construct_finder_sql(options)     end   end end