Forum: Ruby on Rails Fast way of finding new position for model object?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ryan N. (Guest)
on 2006-04-04 01:40
(Received via mailing list)
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
Chris H. (Guest)
on 2006-04-04 02:44
(Received via mailing list)
can i ask why you need to treat this as a list?  what are you using
position
for?
Ryan N. (Guest)
on 2006-04-04 02:57
(Received via mailing list)
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
This topic is locked and can not be replied to.