CODEWITHSUNDEEP
ruby-on-rails-3activerecord
Wes Foster
Wes Foster

Reputation: 8900

Rails Delete DB Records except for the 10 latest entries

I've created a Model called "PostView". Each time a user views a post, it logs it into the database (creating a new record with the user id and post id).

I'm only wanting to retain 10 records per user in the DB. So each time a new record is added, I want to remove all but the latest 10 records.

  1. Is this efficient? If not, what do you suggest as a way to log user/post views?
  2. How can I add the new record and delete all but the latest 10 records in Rails?

Thanks!

Upvotes: 1

Views: 1613

Answers (2)

konyak
konyak

Reputation: 11706

This command queries all records matching user_id and post_id, sorts those records by latest first, and destroys the records in that set that are beyond the 10th latest records.

PostView.where(user_id: user_id, post_id: post_id).
         order('id desc').offset(10).destroy_all

Another solution to your problem is that instead of creating a new PostView record every time, you could update the 10th oldest record if it exists, otherwise create a new record. 

pv = PostView.first(where: ["user_id = ? AND post_id = ?", user_id, post_id], 
                    order: "id desc", offset: 9) || PostView.new
pv.update_attributes(field1: value1, field2: value2, 
                     created_at: Time.now)

Upvotes: 0

Philip Hallstrom
Philip Hallstrom

Reputation: 19879

It seems like this will cause a lot of overhead and that you'd be better off pruning the database via cron on some regular basis or if you really only need the last 10 records, find a more efficient way to store them. But if you can't do that...

cutoff = PostView.where(:user_id => user_id, :post_id => post_id).
                  order('created_at DESC').
                  offset(10).first

PostView.where(:user_id => user_id, :post_id => post_id).
         where(['created_at <= ?', cutoff.created_at]).
         delete_all

Upvotes: 3

Related Questions