CODEWITHSUNDEEP
ruby-on-railsrubyrails-activerecord
LifeOfRPi
LifeOfRPi

Reputation: 113

ActiveRecord: Select max of limit

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.

Upvotes: 8

Views: 15993

Answers (4)

Paul Richter
Paul Richter

Reputation: 11072

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.

Upvotes: 8

New Alexandria
New Alexandria

Reputation: 7324

Should be

YourAR.destroy(YouAR.order(:id).max(:id))

That's untested. The idea is you want to delete the array of IDs returned by something. I don't know if you can do this all in a single query (without just exec'ing SQL).

Upvotes: 0

Zippie
Zippie

Reputation: 6088

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

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

Upvotes: 3

MurifoX
MurifoX

Reputation: 15089

Hmm, maybe this?

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

Upvotes: 1

Related Questions