It''s not clear to me how ActiveRecord is supposed to be used to update a batch of records w/o actually loading them. IOW I want to execute an UPDATE statement something like this: UPDATE database.table SET rcrd_status=''N'' WHERE rcrd_status=''Y'' AND foreign_id=''abcdefg''; find_by_sql seems to be the only way to talk to the db directly, but this process has nothing to do with finding. Also, is there a way to retrieve db & tbl names so they don''t have to be hard coded into such a query? -- gw (www.railsdev.ws) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Well, you need a model, but with a model derived from ActiveRecord, you can use the connection property as... MyModel.connection.execute(some_arbitrarily_hazardous_sql) Good luck! On Nov 23, 2007, at 10:20 PM, Greg Willits wrote:> > It''s not clear to me how ActiveRecord is supposed to be used to > update a batch of records w/o actually loading them. > > IOW I want to execute an UPDATE statement something like this: > > UPDATE database.table SET rcrd_status=''N'' WHERE rcrd_status=''Y'' AND > foreign_id=''abcdefg''; > > find_by_sql seems to be the only way to talk to the db directly, but > this process has nothing to do with finding. > > Also, is there a way to retrieve db & tbl names so they don''t have to > be hard coded into such a query? > > -- gw (www.railsdev.ws) > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 Nov 23, 2007, at 10:41 PM, s.ross wrote:> Well, you need a model, but with a model derived from ActiveRecord, > you can use the connection property as... > > MyModel.connection.execute(some_arbitrarily_hazardous_sql)Great thanks. Been writing arbitrarily hazardous sql for years. So far so good. -- gw --~--~---------~--~----~------------~-------~--~----~ 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 24 Nov 2007, at 06:58, Greg Willits wrote:> > On Nov 23, 2007, at 10:41 PM, s.ross wrote: > >> Well, you need a model, but with a model derived from ActiveRecord, >> you can use the connection property as... >> >> MyModel.connection.execute(some_arbitrarily_hazardous_sql) > > > Great thanks. Been writing arbitrarily hazardous sql for years. So > far so good.There are a few variants on this. First you can use ActiveRecord::Base.connection if you don''t care to nominate a particular model. The point however is that if your models lived in different databases you''d need to pick the right one. The connection adapters have several methods that execute raw sql, of which execute is the most primitive. There''s also select_all (and a few similiar ones), update, and delete, which differ because they return stuff. As far as an update goes you can also do MyModel.update_all "crd_status=''N''", "rcrd_status=''Y'' AND foreign_id=''abcdefg''" Fred --~--~---------~--~----~------------~-------~--~----~ 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 Nov 24, 2007, at 4:20 AM, Frederick Cheung wrote:> > > On 24 Nov 2007, at 06:58, Greg Willits wrote: > >> >> On Nov 23, 2007, at 10:41 PM, s.ross wrote: >> >>> Well, you need a model, but with a model derived from ActiveRecord, >>> you can use the connection property as... >>> >>> MyModel.connection.execute(some_arbitrarily_hazardous_sql) >> >> >> Great thanks. Been writing arbitrarily hazardous sql for years. So >> far so good. > > There are a few variants on this. First you can use > ActiveRecord::Base.connection if you don''t care to nominate a > particular model. The point however is that if your models lived in > different databases you''d need to pick the right one. The connection > adapters have several methods that execute raw sql, of which execute > is the most primitive. There''s also select_all (and a few similiar > ones), update, and delete, which differ because they return stuff. > > As far as an update goes you can also do MyModel.update_all > "crd_status=''N''", "rcrd_status=''Y'' AND foreign_id=''abcdefg''" > > FredUPDATE mydatabase.myrecords SET rcrd_status=''N'' WHERE rcrd_status=''Y'' AND foreign_id=''abcdefg''; I''d recommend that you do something like: Myrecord.update_all([''rcrd_status = ?'', ''N''], [''rcrd_status = ? AND foreign_id = ?'', ''Y'', ''abcdefg'']) Furthermore, if rcrd_status is actually defined as a :boolean, then I''d replace ''N'' with false and ''Y'' with true and let the database adapter sort out how the boolean is implemented. You also don''t have to worry about the right quoting for the foreign_id with this syntax (exactly the same as in a :conditions value on a find). -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Afaik, update_all doesn''t support placeholders. Did something change or did I just miss it somehow initially? I hacked together a helper that did, but update_all would only take conditions as a string, not an array. -Bill Rob Biedenharn wrote:> On Nov 24, 2007, at 4:20 AM, Frederick Cheung wrote: > > >> On 24 Nov 2007, at 06:58, Greg Willits wrote: >> >> >>> On Nov 23, 2007, at 10:41 PM, s.ross wrote: >>> >>> >>>> Well, you need a model, but with a model derived from ActiveRecord, >>>> you can use the connection property as... >>>> >>>> MyModel.connection.execute(some_arbitrarily_hazardous_sql) >>>> >>> Great thanks. Been writing arbitrarily hazardous sql for years. So >>> far so good. >>> >> There are a few variants on this. First you can use >> ActiveRecord::Base.connection if you don''t care to nominate a >> particular model. The point however is that if your models lived in >> different databases you''d need to pick the right one. The connection >> adapters have several methods that execute raw sql, of which execute >> is the most primitive. There''s also select_all (and a few similiar >> ones), update, and delete, which differ because they return stuff. >> >> As far as an update goes you can also do MyModel.update_all >> "crd_status=''N''", "rcrd_status=''Y'' AND foreign_id=''abcdefg''" >> >> Fred >> > > UPDATE mydatabase.myrecords SET rcrd_status=''N'' WHERE rcrd_status=''Y'' > AND > foreign_id=''abcdefg''; > > I''d recommend that you do something like: > > Myrecord.update_all([''rcrd_status = ?'', ''N''], > [''rcrd_status = ? AND foreign_id = ?'', > ''Y'', ''abcdefg'']) > > Furthermore, if rcrd_status is actually defined as a :boolean, then > I''d replace ''N'' with false and ''Y'' with true and let the database > adapter sort out how the boolean is implemented. You also don''t have > to worry about the right quoting for the foreign_id with this syntax > (exactly the same as in a :conditions value on a find). > > -Rob > > Rob Biedenharn http://agileconsultingllc.com > Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org > > > > >--~--~---------~--~----~------------~-------~--~----~ 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 take that back, I just tried it and it worked fine. I must be thinking about something else. :) -Bill William Pratt wrote:> Afaik, update_all doesn''t support placeholders. Did something change > or did I just miss it somehow initially? I hacked together a helper > that did, but update_all would only take conditions as a string, not > an array. > > -Bill > > Rob Biedenharn wrote: >> On Nov 24, 2007, at 4:20 AM, Frederick Cheung wrote: >> >> >>> On 24 Nov 2007, at 06:58, Greg Willits wrote: >>> >>> >>>> On Nov 23, 2007, at 10:41 PM, s.ross wrote: >>>> >>>> >>>>> Well, you need a model, but with a model derived from ActiveRecord, >>>>> you can use the connection property as... >>>>> >>>>> MyModel.connection.execute(some_arbitrarily_hazardous_sql) >>>>> >>>> Great thanks. Been writing arbitrarily hazardous sql for years. So >>>> far so good. >>>> >>> There are a few variants on this. First you can use >>> ActiveRecord::Base.connection if you don''t care to nominate a >>> particular model. The point however is that if your models lived in >>> different databases you''d need to pick the right one. The connection >>> adapters have several methods that execute raw sql, of which execute >>> is the most primitive. There''s also select_all (and a few similiar >>> ones), update, and delete, which differ because they return stuff. >>> >>> As far as an update goes you can also do MyModel.update_all >>> "crd_status=''N''", "rcrd_status=''Y'' AND foreign_id=''abcdefg''" >>> >>> Fred >>> >> >> UPDATE mydatabase.myrecords SET rcrd_status=''N'' WHERE rcrd_status=''Y'' >> AND >> foreign_id=''abcdefg''; >> >> I''d recommend that you do something like: >> >> Myrecord.update_all([''rcrd_status = ?'', ''N''], >> [''rcrd_status = ? AND foreign_id = ?'', >> ''Y'', ''abcdefg'']) >> >> Furthermore, if rcrd_status is actually defined as a :boolean, then >> I''d replace ''N'' with false and ''Y'' with true and let the database >> adapter sort out how the boolean is implemented. You also don''t have >> to worry about the right quoting for the foreign_id with this syntax >> (exactly the same as in a :conditions value on a find). >> >> -Rob >> >> Rob Biedenharn http://agileconsultingllc.com >> Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org >> >> >> >> > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
>> UPDATE mydatabase.myrecords SET rcrd_status=''N'' >> WHERE rcrd_status=''Y'' AND foreign_id=''abcdefg''; > > I''d recommend that you do something like: > > Myrecord.update_all([''rcrd_status = ?'', ''N''], > [''rcrd_status = ? AND foreign_id = ?'', > ''Y'', ''abcdefg''])Ah. I guess I just didn''t see that one staring me in the face (update_all, delete_all).> (exactly the same as in a :conditions value on a find).Speaking of "exactly the same"... I wanted to use this format, but it doesn''t work. Unless I''m just not doing it correctly, I guess Rails doesn''t support this same format that is supported with find? UserPrivilege.update_all( ["rcrd_status = :disabled", {:disabled => ''N''}], ["rcrd_status = :enabled AND foreign_id = :keyval", {:enabled => ''Y'', :keyval => userID}]) -- gw (www.railsdev.ws) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
>>> UPDATE mydatabase.myrecords SET rcrd_status=''N'' >>> WHERE rcrd_status=''Y'' AND foreign_id=''abcdefg''; >> >> I''d recommend that you do something like: >> >> Myrecord.update_all([''rcrd_status = ?'', ''N''], >> [''rcrd_status = ? AND foreign_id = ?'', >> ''Y'', ''abcdefg'']) > > > Ah. I guess I just didn''t see that one staring me in the face > (update_all, delete_all). > > >> (exactly the same as in a :conditions value on a find). > > Speaking of "exactly the same"... I wanted to use this format, but it > doesn''t work. Unless I''m just not doing it correctly, I guess Rails > doesn''t support this same format that is supported with find? > > UserPrivilege.update_all( > ["rcrd_status = :disabled", {:disabled => ''N''}], > ["rcrd_status = :enabled AND foreign_id = :keyval", > {:enabled => ''Y'', :keyval => userID}])DOH! Nevermind -- wrong field name. It works. -- gw (www.railsdev.ws) --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---