Hi,
I''d like to do a "find_by_sql" without a model (e.g.
<model
name>.find_by_sql("...")) as the results I get back are a once off
special,
and I''m happy to handle them as an array. How do I do this?
"ActiveRecord::Base.find_by_sql(...)" does not seem to work?
Background - At the moment I''m doing within a model <model_name>,
however
within one method I need specific information (a group by) about data in A
that will not come back in the form of the model itself. Current I do use
<model_name>.find_by_sql(...) and get back an array which I use. This is
fine however syntactically to me the <model_name> at the beginning is a
bit
silly as the data I get back does not go into the class structure for
<model_name>.
Thanks
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Didn''t try that myself, but some of the methods of the connection adapter should work: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#M001178 select_rows(sql, name = nil) Returns an array of arrays containing the field values. -- Posted via http://www.ruby-forum.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 -~----------~----~----~----~------~----~------~--~---
Hi,> I''d like to do a "find_by_sql" without a model (e.g. <model > name>.find_by_sql("...")) as the results I get back are a once offyou can try to forget about the convenient AR wrapping and use directly the underlying connection. ActiveRecord::Base.connection.execute ''select * from users'' Will return an object representing the resultset. In the case of Mysql you will get a Mysql::Result and you can use the fetch_row method to move along the records retrieved. regards, javier ramírez --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Another way would be to do: Model.find_by_sql(..., :select => [:column_you_want]).map(&:column_you_want) You can remove the map if you don''t want it to be an array. I believe this will work. (haven''t tested it) On May 30, 6:07 am, "Greg Hauptmann" <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > I''d like to do a "find_by_sql" without a model (e.g. <model > name>.find_by_sql("...")) as the results I get back are a once off special, > and I''m happy to handle them as an array. How do I do this? > "ActiveRecord::Base.find_by_sql(...)" does not seem to work? > > Background - At the moment I''m doing within a model <model_name>, however > within one method I need specific information (a group by) about data in A > that will not come back in the form of the model itself. Current I do use > <model_name>.find_by_sql(...) and get back an array which I use. This is > fine however syntactically to me the <model_name> at the beginning is a bit > silly as the data I get back does not go into the class structure for > <model_name>. > > Thanks--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
I can''t see the "Mysql::Result" class in the Rails API doco? I was really after getting basic Ruby Arrays/Hashs back? On Fri, May 30, 2008 at 10:28 PM, javier ramirez <jramirez-7iWCczGtl7hBDgjK7y7TUQ@public.gmane.org> wrote:> > Hi, > > > I''d like to do a "find_by_sql" without a model (e.g. <model > > name>.find_by_sql("...")) as the results I get back are a once off > > you can try to forget about the convenient AR wrapping and use directly > the underlying connection. > > ActiveRecord::Base.connection.execute ''select * from users'' > > Will return an object representing the resultset. In the case of Mysql > you will get a Mysql::Result and you can use the fetch_row method to > move along the records retrieved. > > regards, > > javier ramírez > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 May 30, 10:57 pm, "Greg Hauptmann" <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I can''t see the "Mysql::Result" class in the Rails API doco? I was really > after getting basic Ruby Arrays/Hashs back? >ActiveRecord::Base.connection.select_all returns an array of hashes. (sometimes select_values and some of the other select_xxx methods are also useful). Fred> On Fri, May 30, 2008 at 10:28 PM, javier ramirez <jrami...-7iWCczGtl7hBDgjK7y7TUQ@public.gmane.org> > wrote: > > > > > Hi, > > > > I''d like to do a "find_by_sql" without a model (e.g. <model > > > name>.find_by_sql("...")) as the results I get back are a once off > > > you can try to forget about the convenient AR wrapping and use directly > > the underlying connection. > > > ActiveRecord::Base.connection.execute ''select * from users'' > > > Will return an object representing the resultset. In the case of Mysql > > you will get a Mysql::Result and you can use the fetch_row method to > > move along the records retrieved. > > > regards, > > > javier ramírez--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
excellent, thanks On Sat, May 31, 2008 at 8:11 AM, Frederick Cheung < frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > > On May 30, 10:57 pm, "Greg Hauptmann" <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > I can''t see the "Mysql::Result" class in the Rails API doco? I was > really > > after getting basic Ruby Arrays/Hashs back? > > > ActiveRecord::Base.connection.select_all returns an array of hashes. > (sometimes select_values and some of the other select_xxx methods are > also useful). > > Fred > > > On Fri, May 30, 2008 at 10:28 PM, javier ramirez <jrami...-7iWCczGtl7hBDgjK7y7TUQ@public.gmane.org> > > wrote: > > > > > > > > > Hi, > > > > > > I''d like to do a "find_by_sql" without a model (e.g. <model > > > > name>.find_by_sql("...")) as the results I get back are a once off > > > > > you can try to forget about the convenient AR wrapping and use directly > > > the underlying connection. > > > > > ActiveRecord::Base.connection.execute ''select * from users'' > > > > > Will return an object representing the resultset. In the case of Mysql > > > you will get a Mysql::Result and you can use the fetch_row method to > > > move along the records retrieved. > > > > > regards, > > > > > javier ramírez > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
goood +1 point, I learnt more one new thing. thanks Reinhart -- Posted via http://www.ruby-forum.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 -~----------~----~----~----~------~----~------~--~---
actually with "ActiveRecord::Base.connection.select_all" it doesn''t seem to allow use of "?" to populate variables - does this mean one has to manually "sanitize" the SQL for security, and if so which calls would you normally apply over the sql string you plan on using for "ActiveRecord::Base.connection.select_all"? thanks On Sat, May 31, 2008 at 8:11 AM, Frederick Cheung < frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > On May 30, 10:57 pm, "Greg Hauptmann" <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > I can''t see the "Mysql::Result" class in the Rails API doco? I was > really > > after getting basic Ruby Arrays/Hashs back? > > > ActiveRecord::Base.connection.select_all returns an array of hashes. > (sometimes select_values and some of the other select_xxx methods are > also useful). > > Fred > > > On Fri, May 30, 2008 at 10:28 PM, javier ramirez <jrami...-7iWCczGtl7hBDgjK7y7TUQ@public.gmane.org> > > wrote: > > > > > > > > > Hi, > > > > > > I''d like to do a "find_by_sql" without a model (e.g. <model > > > > name>.find_by_sql("...")) as the results I get back are a once off > > > > > you can try to forget about the convenient AR wrapping and use directly > > > the underlying connection. > > > > > ActiveRecord::Base.connection.execute ''select * from users'' > > > > > Will return an object representing the resultset. In the case of Mysql > > > you will get a Mysql::Result and you can use the fetch_row method to > > > move along the records retrieved. > > > > > regards, > > > > > javier ramírez > > >--~--~---------~--~----~------------~-------~--~----~ 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 Jun 1, 7:01 am, "Greg Hauptmann" <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> actually with "ActiveRecord::Base.connection.select_all" it doesn''t seem to > allow use of "?" to populate variables - does this mean one has to manually > "sanitize" the SQL for security, and if so which calls would you normally > apply over the sql string you plan on using for > "ActiveRecord::Base.connection.select_all"? > thanksYou can use sanitize_sql to expand some conditions (it''s a protected method, so you either need to be calling this from a cass method of an ActiveRecord subclass or use send), or you can use connection.quote to quote a parameter. Fred> > On Sat, May 31, 2008 at 8:11 AM, Frederick Cheung < > > frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > On May 30, 10:57 pm, "Greg Hauptmann" <greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > > > I can''t see the "Mysql::Result" class in the Rails API doco? I was > > really > > > after getting basic Ruby Arrays/Hashs back? > > > ActiveRecord::Base.connection.select_all returns an array of hashes. > > (sometimes select_values and some of the other select_xxx methods are > > also useful). > > > Fred > > > > On Fri, May 30, 2008 at 10:28 PM, javier ramirez <jrami...-7iWCczGtl7hBDgjK7y7TUQ@public.gmane.org> > > > wrote: > > > > > Hi, > > > > > > I''d like to do a "find_by_sql" without a model (e.g. <model > > > > > name>.find_by_sql("...")) as the results I get back are a once off > > > > > you can try to forget about the convenient AR wrapping and use directly > > > > the underlying connection. > > > > > ActiveRecord::Base.connection.execute ''select * from users'' > > > > > Will return an object representing the resultset. In the case of Mysql > > > > you will get a Mysql::Result and you can use the fetch_row method to > > > > move along the records retrieved. > > > > > regards, > > > > > javier ramírez--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---