Getting close to the database in Rails

Sometimes ActiveRecord wraps your data up too much, and you don’t want or need all that convenient but processor-cycle consuming abstraction.We had a case where we wanted to delete nearly 3 million records from our database. The conditions for deletion were a little complex, and writing a single SQL query for it was not practical.So it’s nice to do it in Ruby, but instantiating 3m ActiveRecord objects is just not an option. Well, we didn’t need to – you can get data more directly from the database by talking directly to the (mysql in our case) connection.Here’s how to get a list of ids from the database that were last updated last year.

result = ModelClass.connection.execute("SELECT id FROM table            WHERE updated_at < '2008-01-01'")
ids = []result.each {|row|  ids << row[0].to_i}

Then you can go through the ids and do what you like with them, perhaps compare them to other lists of ids from other tables (which is what we did).Deleting the records is easy, just use ModelClass.delete(the_id) which works without instantiating an object. Use with care!

Advertisements

One thought on “Getting close to the database in Rails

  1. […] bookmarks tagged database Getting close to the database in Rails&nbsp;saved by 4 others &nbsp;&nbsp;&nbsp;&nbsp;Olivia230 bookmarked on 01/28/08 | […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s