next object problem on rails

Videos table

id
title
votes_count

Videos cont开发者_运维技巧roller

def show
 @video = Video.find(params[:id])
 @next_video = Video.order("votes_count DESC").where("votes_count < ?", @video.votes_count).first
 @previous_video = Video.order("votes_count ASC").where("votes_count > ?", @video.votes_count).first
end

The problem is that there are videos that have the same votes_count number. When i change votes_count< to votes_count<= it starts to loop between 2 videos. Any ideas? Thanks.

---

The main key to solving this problem is that you need to explicitly include a secondary sort field, that will give you the certainty to resolve the issue of rows with the same votes_count. You also need to split up the >= into two separate clauses because you only want to evaluate the secondary field when the primary is =.

Now, for bonus points, I'm also going to refactor your code as methods on your model, so your controller becomes just...

def show
  @video = Video.find params[:id]
end

And your model becomes...

def next
  self.class.
    order( "votes_count, id" ).
    where( "votes_count > :votes_count OR ( votes_count = :votes_count AND id > :id )", attributes.symbolize_keys ).first
end

def previous
  self.class.
    order( "votes_count DESC, id DESC" ).
    where( "votes_count < :votes_count OR ( votes_count = :votes_count AND id < :id )", attributes.symbolize_keys ).first
end

And now in your view you can just refer to @video.next and @video.previous

---

Lets add some new stuff into your Video model:

class Video < ActiveRecord::Base
  def next(column = 'votes_count', order = 'DESC')
    pos = self.position(column, order)
    on_position(pos - 1, column, order) if pos > 0
  end

  def previous(column = 'votes_count', order = 'DESC')
    pos = self.position(column, order)
    on_position(pos + 1, column, order)
  end

  private

  def position(column, order)
    order_by = "#{column} #{order}"
    arrow = order.capitalize == "ASC" ? "<=" : "=>"
    Video.where("#{column} #{arrow} (?)", self.send(column)).order(order_by).count
  end

  def on_position(pos, column, order)
    order_by = "#{column} #{order}"
    arrow = order.capitalize == "ASC" ? "<=" : "=>"
    Video.order(order_by).offset(pos).first
  end

end

And your controller

def show
  @video = Video.find(params[:id])
  @next_video = @video.next
  @previous_video = @video.previous
end

Attention, I haven't tested it, so it can be buggy. All errors put in comments :)

---

You need to prevent it from returning the same video

@next_video = Video.order("votes_count DESC").where("votes_count < ? AND id !=", @video.votes_count, @video.id).first

You should also probably add a secondary ordering (possibly by id, or created_at) so that the order is consistent when the number of votes is equal.