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