How change records w/o loading them?

rubyonrails-talk
1

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)

2

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!

3

Great thanks. Been writing arbitrarily hazardous sql for years. So far so good.

-- gw

4

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

5

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@AgileConsultingLLC.com

6

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:

7

I take that back, I just tried it and it worked fine. I must be thinking about something else. :slight_smile:

-Bill

William Pratt wrote:

8

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)

9

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)