Fast way of finding new position for model object?


#1

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


#2

can i ask why you need to treat this as a list? what are you using
position
for?


#3

The position column is basically a cache of the bug’s ranking. Having
this model act as a list let’s me slice and filter the objects while
still being able to quickly retrieve and display each object’s ranking
in the overall list.


Ryan