Getting the results of AR's generated SQL

Has anyone tackled the idea of stored queries, like for reporting? I'd like to use AR to build a query then save the query off for future use. I've been looking in the AR API and doing some googling, but what I'm finding suggests that AR was not designed to give the SQL back to the application. I found a very interesting blog post by Jamis Buck (in November of 2006) that gives some details about the SQL generation process. There is a method called construct_finder_sql, but it's relatively deep in the process.

Does anyone know how to accomplish this or can point me to a resource that describes what I'm wanting to do?

Thanks,
Phillip

If your database supports views or stored procedures you could use
either of those. Otherwise you can add custom finders to your models.

I think the general consensus with the Rails core is that stored
procedures are evil:

http://web.archive.org/web/20060418215514/http://www.loudthinking.com/arc/000516.html

so I guess that leaves custom finders and views.

I didn't explain clearly enough what I am thinking. Suppose you have an application in which you allow the user to create custom queries so they can export data in CSV format for use elsewhere. Obviously, you don't want the user to have to regenerate the query (whether by some spiffy UI or whatever) every time they want fresh data. Ideally, you save the query somewhere for reuse. Now, I could go through the trouble of generating the query myself, but if AR is already doing it, it would be nice to not reinvent the wheel.

This isn't the exact situation I'm thinking of, but it does illustrate the concept. I'm thinking something much more dynamic than stored procedures and views.

Peace,
Phillip

But that's just a UI issue, which could be solved by saving the
contents of the UI. It would just form part of the "best practice" of
remembering what the user has done in the past.

There must be some other drawback with "regenerating the query" that
I'm not seeing.

///ark

Hm. I'm doing very bad at explaining this. Maybe I was trying to be too generic. Here is my actual use case:

I have a search form that the user can key in criteria in a bunch of different fields. Supposing the user might want to search on these same values more than once, I thought it would be nice to save off the query so all of the values wouldn't have to be keyed in again. Since AR already goes through the pain of sanitizing and quoting and all of that, I thought it would be nice if I could "capture" the SQL in the form that it will be submitted to the database in. I could then save it in a table and the user could give it a name. The next time the user wants to run that particular query, instead of typing in all of the values, s/he could just pick the query from a list.

I know how to build the SQL myself, but why reinvent the wheel?

Does that make more sense?

Now, to your suggestion, Mark. Are you saying I could save the serialized form, then deserialize it somehow? That might work.

Peace,
Phillip

Nothing that complicated. I think I would just save the values of each
control on the form. The user selects a saved query and the form is
populated with those values. The user can change them, save them as a
new query, and run the query. The form values may be more meaningful
to users than the actual SQL.

Just some thoughts...

///ark

Hi ///ark :slight_smile:

I appreciate your thoughts. I thought about this, but it's not nearly as versatile. If I have 10 search forms in my app, I need to either have 10 tables that reflect the UI fields that can be saved or I have to come up with a way to pack and unpack them. If I had the SQL, I could save it in a single table with a name and a search form reference. The user will never know about the SQL.

Anyway, the question I really want answered is whether it's possible to capture the SQL from AR. Why I want to is really secondary. I am generally a "wheel reinventer" (ha ha), but this time I'd rather not. That might be because I know how complicated generating SQL can be.

Thanks for your thoughts.

Peace,
Phillip

The trouble with that, I think, is that you have no way to show the
user what's actually being searched for. You have to trust that the
user correctly remembers what a particular named query does. This is
more or less important depending on the accuracy of the search
results.

But that's not what you're asking. :slight_smile:

The logs, of course, contain the SQL that's being sent to the
database, but that's probably not very workable. Every query goes
through a connection object. Perhaps you could hook into that.

///ark

Hi ///ark (et al),

The trouble with that, I think, is that you have no way to show the
user what's actually being searched for. You have to trust that the
user correctly remembers what a particular named query does. This is
more or less important depending on the accuracy of the search
results.

That's a good point. It would be a UI nicety for the user to click
on a particular saved query and have the fields in the form
populated. That would make it very easy to "clone" new queries.

But that's not what you're asking. :slight_smile:

No, but the thoughts are appreciated anyway. I was merely trying to
avoid getting turned too far away from the original question. Thanks
for playing along :slight_smile:

The logs, of course, contain the SQL that's being sent to the
database, but that's probably not very workable. Every query goes
through a connection object. Perhaps you could hook into that.

Thanks for suggesting the hook into connection. I didn't even think
about going that route. It turns out that I didn't have to go that
deep, which is good. [I was poking all the way down in the PostgreSQL
adapter trying to get a feel for how it all works!] I finally
figured out a way to do it, and it's really not that hard.

I created lib/ar_base_extend.rb, which contains

class ActiveRecord::Base
  def self.find_by_and_return_sql(sql)
    sanitized_sql = sanitize_sql(sql)
    return find_by_sql(sql), sanitized_sql
  end
end

I required the file in environment.rb. In my models, I can now call
find_by_and_return_sql instead of find_by_sql when I want the
statement returned. So if I'm doing an order query, I would do

s = <some crazy sql statement with params>
p = {hash of params and values}

results, sql = Order.find_by_and_return_sql([s, p])

# save sql

return results

Woohoo!

///ark

Peace,
Phillip

I just realized a minor improvement to my find_by_and_return_sql method. I originally did this

def self.find_by_and_return_sql(sql)
  sanitized_sql = sanitize_sql(sql)
  return find_by_sql(sql), sanitized_sql
end

but remembered that find_by_sql calls sanitize_sql, which just returns a string if a string is provided. To say it a different way, since I called sanitize_sql already, I don't need to have AR call it again, so I changed my method to this

def self.find_by_and_return_sql(sql)
  sanitized_sql = sanitize_sql(sql)
  return find_by_sql(sanitized_sql), sanitized_sql
end

That will save a few processor cycles.

Peace,
Phillip