# Getting the results of AR's generated SQL **URL:** \ **Category:** Rails\ **Created:** [December 28, 2007, 5:32pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436 "2007-12-28T17:32:19Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 28, 2007, 5:32pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/1 "2007-12-28T17:32:19Z")
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 B. (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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 31, 2007, 5:36pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/2 "2007-12-31T17:36:06Z")
On 12/28/07, Phillip K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Does anyone know how to accomplish this or can point me to a resource > that describes what I’m wanting to do? 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: > **[Choose a single layer of cleverness (Loud Thinking)](https://web.archive.org/web/20060418215514/http://www.loudthinking.com/arc/000516.html)** > > Personal weblog for David Heinemeier Hansson so I guess that leaves custom finders and views. – Greg D. [http://destiney.com/](http://destiney.com/) ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 31, 2007, 7:46pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/3 "2007-12-31T19:46:38Z")
On Dec 31, 2007, at 10:35 AM, Greg D. wrote: > > construct\_finder\_sql, but it’s relatively deep in the process. > > [Wayback Machine](http://web.archive.org/web/20060418215514/http://) > > [www.loudthinking.com/arc/000516.html](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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 31, 2007, 8:02pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/4 "2007-12-31T20:02:26Z")
On Dec 31, 10:45 am, Phillip K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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. 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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 31, 2007, 8:49pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/5 "2007-12-31T20:49:46Z")
On Dec 31, 2007, at 1:01 PM, Mark W. wrote: > 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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 31, 2007, 11:55pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/6 "2007-12-31T23:55:00Z")
On Dec 31, 2007, at 4:33 PM, Mark W. wrote: > to users than the actual SQL. > > Just some thoughts… Hi ///ark 🙂 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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [December 31, 2007, 11:34pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/7 "2007-12-31T23:34:33Z")
On Dec 31, 11:48 am, Phillip K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Now, to your suggestion, Mark. Are you saying I could save the > serialized form, then deserialize it somehow? That might work. 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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [January 1, 2008, 12:37am UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/8 "2008-01-01T00:37:38Z")
On Dec 31, 2:54 pm, Phillip K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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. 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. 🙂 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 ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [January 1, 2008, 3:26am UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/9 "2008-01-01T03:26:58Z")
Hi ///ark (et al), On Dec 31, 2007, at 5:36 PM, Mark W. wrote: > 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. 🙂 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 🙂 > 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 = p = {hash of params and values} results, sql = Order.find\_by\_and\_return\_sql([s, p]) # save sql return results Woohoo! > ///ark Peace, Phillip ---
**Author:** ![pkoebbe](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@pkoebbe](https://www.ruby-forum.com/u/pkoebbe)\ **Post date:** [January 1, 2008, 1:59pm UTC](https://www.ruby-forum.com/t/getting-the-results-of-ars-generated-sql/124436/10 "2008-01-01T13:59:59Z")
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