Recalculate Counter Cache of 120k Records [Rails / ActiveRecord]

Question

The following situation:

I have a poi model, which has many pictures (1:n). I want to recalculate the counter_cache column, because the values are inconsistent.

I've tried to iterate within ruby over each record, but this takes much too long and quits sometimes with some "segmentation fault" bugs.

So i wonder, if its possible to do this with a raw sql query?

Answer 1

If, for example, you have Post and Picture models, and post has_many :pictures, you can do it with update_all :

Post.update_all("pictures_count=(Select count(*) from pictures where pictures.post_id=posts.id)")