Finder SQL to string

A lot of plugins would be easier if there was a way, instead of
executing a find, to get the SQL it would use as a String (or perhaps
an array of a string and bind variables).

This would make it easier to do a lot of powerful queries at the
database, while still taking advantage of Rails' to manage the basic
structure. Make it easier to DRY up some of those tough finder
methods.

Any way of doing this? What are others' thoughts about the utility of
this?

I've been working on ActiveRecord patches this week so I know a bit
about its internals. The function you want is
ActiveRecord::Base.sanitize_sql(). Given a find()-condition-like
parameter (such as ['foo = ?', bar], {:foo => :bar}, and things like
that) it will return a piece of SQL.

I use sanitize_sql all the time. But that's not what I meant. Let me
illustrate:

Product.get_sql_of {Product.find(:conditions => 'cost < 3')}
# => 'SELECT * FROM products WHERE cost < 3'

It's easy for that case, but for complicated finders, and especially
custom finder methods (eg Product.find_on_sale), it can be very
helpful, if you want to make more powerful queries and reports.

An rough example might be taking a method Product.find_on_sale and
writing a metaquery to let us know who bought any of those items, or
some stats on the top selling all items.

This is not possible without modifying ActiveRecord. You'll have to
modify the find_one, find_every, or find_by_sql methods to attach that
information to all the objects returned by a query.

This should work:

class << ActiveRecord::Base.connection
  def execute_with_capture(*args)
    @queries ||= []
    @queries << args
    execute_without_capture(*args)
  end
  alias_method_chain :execute, :capture
end

ActiveRecord::Base.connection.instance_variable_get("@queries")

Dan Manges
http://www.dcmanges.com/blog

I'm not sure, but I think that would leak memory like crazy :slight_smile:

You mean endlessly collecting queries is a problem? :slight_smile:
Code without memory leak: http://pastie.caboo.se/62939

Something like this should work for what you need Robert, but you may
need to make changes depending on how you want to use it.

Dan Manges
http://www.dcmanges.com/blog

I wrote a plugin to provide custom SQL when doing a find with
association eager loading. It was certainly hard to get into
ActiveRecord to make it happen. In many cases I either found the
generated SQL to be incorrect (for my purposes, anyway) and certainly
non-optimal.

It certainly allowed me to continue to make full use of the AR
goodness while still allowing me to get my finder methods to work for
me.

Check out my blog entry on the plugin: http://kellogg-assoc.com/articles/2006/11/05/eager-finder-sql

Gregg Kellogg
http://kellogg-assoc.com