ActiveRecord: Select max of limit

Question

I'm trying to convert the following SQL query to ActiveRecord and it's melting my brain.

delete from table where id <= (
select max(id) from (
    select id from table order by id limit 0, 10));

Any ideas?

What I'm trying to do is limit the number of rows in the table. So, I want to delete anything that is less than the most recent 10 entries.

edit: Found a solution by combining several of the below answers.

Temperature.where('id < ?', Temperature.order('id DESC').limit(10).pluck(:id).min).destroy_all

This leaves me with the latest 10 entries.

Answer 1

From your SQL, it looks like you want to delete the first 10 records from the table. I believe most of the answers so far will so that. Here are two additional alternatives:

Based on MurifoX's version:

Table.where(:id=>Table.order(:id).limit(10).pluck(:id)).destroy_all

This will effectively perform a WHERE id IN (...)

Slightly shorter version:

Table.order(:id).limit(10).destroy_all

This will simply grab the first 10 records and destroy

EDIT: To grab all but the most recent X number of records (10 for example), only way I can think of to do that at the moment is this:

 Table.order(:id).limit(Table.count - 10).destroy_all if Table.count > 10

In essence, grab the oldest records, leaving only the 10 most recent, and destroy them, assuming the table size is greater than 10. Maybe there's a cleaner way to do it, but that's the only way I can imagine doing this right now.

Answer 2

model = Model.order(:id).limit(10).last

Model.where("id <= ?", model.id).destroy_all

Answer 3

Hmm, maybe this?

Model.destroy(Model.where('id <= ?', Model.order(:id).limit(10).pluck(:id).max))