Rails 4 : ActiveRecord Delete With Offset

Question

I have a program submitting an HTTP request to my Rails application every five minutes. These records have an index on ID & created_at to display in reverse order. Due to the frequency of these status updates, I only want to store the most recent ten of them. While researching how to do this, I figured an offset would be perfect to retrieve and then delete a dataset. Though in my rails console tests, I haven't been able to convince ActiveRecord that it wants to work that way (it deletes all of the records instead of just the ones not included in offset).

Is there a more efficient way to do what I'm trying to do in my controller? And is there a way to delete records with an offset using ActiveRecords? If not, what would be the ideal way to go about doing it?

def create
    env = Env.find_by_name(params[:updateresult][:env_name])
    @updateresult = env.updateresults.build(updateresult_params)
    respond_to do |format|
        if @updateresult.save
            format.json { render json: @updatresults, status: :created}
            #env.updateresults.where(:id => env.updateresults.offset(10).pluck(:id)).delete_all
        else
            format.json { render json: @updatresults.errors, status: :unprocessable_entity }
        end
    end
end

Edit: Altered David's answer to come up with the commented out answer in the above code

Mhhh I don't get exactly what you mean but YourModel.where(something).limit(10).destroy_all should be enough for the task you are asking, not to mention the fact that destroying 10 records even if it's every 5 minutes is far from giving you any trouble, that's micro optimization and you shouldn't do it — Francesco Belladonna, Feb 12 '14 at 21:00
@Fire-Dragon-DoL I think the logic is not that the earliest 10 records should be deleted, but that all but the most recent 10 records should be deleted. — David Aldridge, Feb 12 '14 at 21:02
Go with Davis suggestion if you **need** to use delete_all instead of destroy_all. I won't suggest you this by the way because it will ignore any callbacks on those objects. — Francesco Belladonna, Feb 12 '14 at 22:36
Have you checked this Q&A http://stackoverflow.com/a/24685370/1408866 ? — konyak, Jul 10 '14 at 20:16

score 5 · Accepted Answer · answered Feb 12 '14 at 21:00

5

I don't think that you can use an offset in a delete, so you'd probably have to:

delete from my_table
where id in (  select id
                 from my_table
             order by created_as desc
               offset 10);

So:

MyTable.where(:id => MyTable.order("created_at desc").offset(10).pluck(:id)).delete_all

Of course you could just delete records more than 50 minutes old?

answered Feb 12 '14 at 21:00

David Aldridge

51,479
8
68
96

Can't delete records more than fifty minutes old, as I may have a day old record that conveys helpful info! Though this looks solid, I'll give this a whirl some time shortly and come back with my findings. Thanks! – user1870954 Feb 12 '14 at 21:08
okay so! I've needed to adapt your example some, as MyTable belongs_to : has_many of another table (to be clear, MyTable is the has_many in this instance). Is it possible to do something akin to the above in a more has_many friendly way? – user1870954 Feb 13 '14 at 16:04
Scratch that! After a few permutations I ended up with HasManyTable.BelongsToTable.where(:id => HasManyTable.BelongsToTable.offset(10)).delete_all – user1870954 Feb 13 '14 at 16:21
I'd always make sure there was an order defined on the query if you're using limit or offset, by the way. – David Aldridge Feb 13 '14 at 17:55
Is that for clarity's sake? Or perhaps in case I change the index on the table? I can definitely do that, but I suspected that the index on the table was sufficient. I like learning! – user1870954 Feb 13 '14 at 18:41
1

Never rely on implicit sort orders, especially when you're assuming that the query optimiser will provide one in the absence of an ORDER BY -- neither DISTINCT nor GROUP BY should be relied on. Activerecord's first and last methods are not foolproof either -- in 3.2.13, first against PostgreSQL does not place an "order by id" on the query, although last does. – David Aldridge Feb 13 '14 at 18:48
Thanks David, I've added the ordering. Everything works great on localhost, but when I deploy it to Heroku I receive the following error. DELETE FROM "updateresults" WHERE "updateresults"."env_name" = $1 AND "updateresults"."id" IN (SELECT "updateresults"."id" FROM "updateresults" WHERE "updateresults"."env_name" = $1 ORDER BY created_at DESC OFFSET 10)): ActiveRecord::StatementInvalid (PG::Error: ERROR: bind message supplies 2 parameters, but prepared statement "a6" requires 1 Is this something to do with PostgreSQL and how it binds parameters? – user1870954 Feb 14 '14 at 19:20
env.updateresults.where(:id => env.updateresults.order("created_at desc").offset(10)).delete_all – user1870954 Feb 14 '14 at 20:03
1

Hmmm, not sure. Maybe pluck the required id's in a separate step and pop them into the where clause as an array. – David Aldridge Feb 14 '14 at 21:23
I'm silly, I ditched the pluck when I found the above comment working in rails console with sqllite. Adding the pluck back in was the success I needed. Thanks again! – user1870954 Feb 18 '14 at 19:02

Rails 4 : ActiveRecord Delete With Offset

1 Answers1