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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 12/28/07, Phillip Koebbe <phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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?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. -- Greg Donald http://destiney.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 31, 2007, at 10:35 AM, Greg Donald wrote:> > On 12/28/07, Phillip Koebbe <phillipkoebbe-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> 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? > > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 31, 10:45 am, Phillip Koebbe <phillipkoe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 31, 2007, at 1:01 PM, Mark Wilden wrote:> > On Dec 31, 10:45 am, Phillip Koebbe <phillipkoe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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. > > ///arkHm. 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 31, 11:48 am, Phillip Koebbe <phillipkoe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 31, 2007, at 4:33 PM, Mark Wilden wrote:> > On Dec 31, 11:48 am, Phillip Koebbe <phillipkoe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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...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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 31, 2:54 pm, Phillip Koebbe <phillipkoe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi ///ark (et al), On Dec 31, 2007, at 5:36 PM, Mark Wilden 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 = <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> >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---