I have a table that retains strings to display in a select box. The
select box is supposed to display only the most recently added 10
Strings (there is a field, tstamp, which holds the timestamp of the
most recent change to a row).
So there’s never a reason for the table to hold more than 10 strings.
The issue – my question – is how do I keep only the most recent 10
records in the db, efficiently? This issue must be faced by many, and
many times over. Rather than try to reinvent the wheel here, can
anyone tell me how this sort of thing is typically handled? Thanks,
RVince
Simon, Is there a way to do it all in one sql statement? May I ask how
you implement this in Ruby/.Rails, say, doing it when something occurs
(like login/logout?) Thanks Rvince
Ah…wonderful. Exacly what I was hoping. I was afraid I would have
to put out a series of straight sql, but once again, ruby/rails has a
better way. Thanks Simon!
Hmmm, I think that just throwing out records older than week wont work
for me – I need to keep, say, 5 or 10 of them. How can I specify
this? THanks -Rvince
Hmmm, I think that just throwing out records older than week wont work
for me – I need to keep, say, 5 or 10 of them. How can I specify
this? THanks -Rvince
Look into using a limit clause on your delete operation.
I’m assuming you have an ActiveRecord model that is managing this
table. If so, you could handle the delete in that model, perhaps with
a callback (after_save). (Again, without knowing the details of the
application, this may or may not be a good approach performance wise
depending on how much activity is on the table.)
You could also just schedule a job to periodically clean it up and
then define a default scope on the model that limits it to the ten
most recent records. Assuming you have timestamp columns (updated_at
specifically), this is a trivial sort/limit.
Is created_at the standard way of doing that (I alwasy put in certain
fields in my tables – a timestamp field, a crossref field – which is
a string that would be the index of the field in another db whose
structure might be trying to follow mine or vice versa).
Additionally, I’m not even sure how to perform this in my rails app. I
see an ActiveRecord.find_by_sql and count_by_sql but there is nothing
more generic whic hwould allow me to perform an SQL delete statement
out of rails – is there?
Thanks for your help on this guys. I had a hard time getting my head
around this and am most grateful to your help! -RVince.
Yes, I can see there is no easy, rails-way to do this. In effect, I
need to
read in the X most recent records (Select * from Chanelnotes order
by tstamp DESC limit 10)
Delete the entire table (delete * in Channelnotes)
Do an insert on the ten records I read in.
There’s no other way.
Sure there is. Find the IDs of the 10 most recent records, then delete
any record whose ID is not in that set. SQL would be something like
(from memory):
DELETE FROM channelnotes
WHERE id NOT IN
(SELECT id from channelnotes
ORDER BY tstamp
LIMIT 10)
See? You only touch the records you’re deleting. No reinsert
necessary.
BTW, why is your timestamp column called tstamp instead of created_at ?
Is created_at the standard way of doing that (I alwasy put in certain
fields in my tables – a timestamp field, a crossref field – which is
a string that would be the index of the field in another db whose
structure might be trying to follow mine or vice versa).
if your timestamp is called created_at or updated_at Rails will set it
for you
Additionally, I’m not even sure how to perform this in my rails app. I
see an ActiveRecord.find_by_sql and count_by_sql but there is nothing
more generic whic hwould allow me to perform an SQL delete statement
out of rails – is there?
There is delete_all but that expects merely a set of sql conditions.
ActiveRecord::Base.connection.execute “…” allows you to execute
arbitrary sql statements
ActiveRecord::Base.connection.delete is nearly identical to that (it
calls execute) but you may prefer the explicitness of calling a method
called delete, more importantly using the delete method will flush
rails’ sql cache.