I have a model object that acts as a list. The position of each object
is determined by two factors: number of votes ascending and age of
object descending. When a user adds or deletes a vote I need to
quickly update the associated model object’s position. Right now this
is done with the following method:
def update_position
position = nil
Bug.find(:all, :order => "votes_count DESC, id
ASC").each_with_index{ |bug,i|
if bug.id == self.id
position = i
break
end
}
self.insert_at(position)
end
Basically, find all of the bugs in the order in which they’re wanted
and then iterate through each until the new position of this bug is
found. When it’s found, save the position, break out of the loop and
insert the current model into the correct position.
Unfortunately, this method is rather slow. I broke the
Bug.find().each_with_index() line into two separate lines so that I
could easily benchmark the method and see what was sucking up all of
my time. Here are the results:
user system total real
find_all 1.250000 0.030000 1.280000 ( 1.308910)
each_with_index 0.070000 0.000000 0.070000 ( 0.076316)
The find_all can take anywhere from 1.1 - 2.3 seconds on a table with
2000 rows, in my experiments.
Is there a more efficient way to determine the new position of a row?
I had hoped there was some magical sql query I could use like SELECT POSITION(*) FROM bugs WHERE id = #{bug.id} that would spit out
correct position in one quick call. Or that I could find the rows
around a specific record when they’re sorted in the order I need.
Those both seem to be pipe dreams, however.
Anyone have any suggestions as to how I can quickly find the new
position of an object in a list, with my given sorting criteria?
–
Ryan