How change records w/o loading them?

Rails
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

On 24 Nov 2007, at 06:58, Greg W. wrote:

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

4

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

5

On Nov 24, 2007, at 4:20 AM, Frederick C. wrote:

MyModel.connection.execute(some_arbitrarily_hazardous_sql)
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 B. http://agileconsultingllc.com
[email protected]

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

7

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

-Bill

8

Ah. I guess I just didn’t see that one staring me in the face
[“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)

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}])

– gw (www.railsdev.ws)