Position of object in database

Question

Hi!

I have got model Team and I've got (i.e.) team = Team.first :offset => 20. Now I need to get number of position of my team in db table.

I can do it in ruby:

Team.all.index team #=> 20

But I am sure that I can write it on SQL and it will be less expensive for me with big tables.

Answer 1

I'm not sure I'm following along, but will position value be used a lot it might be worth making sure it is set on save. Will a team change position over time or is it static?

I don't think the position of a record in the database is one of the things a database really cares for, that kind of data should be up to you to set.

Depending on the database you may be able to use the LIMIT clause, but you can't use it to find the position of a certain row. Just to select rows number 20 to 21 for example, but it'll differ depending on sorting order.

SELECT * FROM teams LIMIT 20,21

I think setting a position attribute on your model or having a join model is the way to go.

Answer 2

Assuming the order is made by ID desc:

class Team < ActiveRecord::Base
  def position
    self.class.count(:conditions => ['id <= ?', self.id]) 
  end
end