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: Parked at Loopia

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