Ordering of has_and_belongs_to_many associations

Question

In my rails app I have two models that are related by has_and_belongs_to_many. This means there is a join table.

Imagine the scenario where I am adding users to a game. If I want to add a user, I do:

@game.users << @user

Supposing that I want to know in what order I added these users. I can do this:

@game.users.each do....

My questions are:

1. Is the ordering if this list guaranteed to read the same way each time?

2. If that's the case, What's a clean way to reorder the users in the game?

Klockau

has_and_belongs_to_many :users, -> { order('users.id ASC') } – Anarthria

Answer 1

To expand on the bottom of danivo's answer:

If you want to order by the time they are added to this list then I recommend that instead of using a has_and_belongs_to_many you actually use a has_many :through:

game.rb

has_many :played_games
has_many :users, :through => :played_games, :order => "played_games.created_at ASC"

user.rb

has_many :played_games
has_many :games, :through => :played_games

played_game.rb

belongs_to :game
belongs_to :user

Of course, changes pending on the names...

In the played_games table if you have a column called created_at the second has_many in games will order by this field and return the users in the order of which they were added to the game.

Alfaro

I think this can work for me... to give more context - I don't need to sort by user name, rather I want the server to randomly pick an order so that player positions in the game are decided by it. So I guess I can put a 'player_index' field in the played_game table, and supply indices at the point I want to decide who moves first.... – Klockau

Answer 2

I'm not certain but I would say that the order is as guaranteed to be the same as your database guarantees the order of the result set without an order by clause.

You can add a :order => "last_name, first_name asc" to the :has_and_belongs_to_many relationship statement. This will set a default behavior for the order of the items that comes back.

You can also do @game.users.find(:all, :order => "last_name, first_name asc") or create named scopes for common ordering that you will need. Check out the api for details

If knowing the order you added them is really important, you probably want to switch to a has_many :through relationship so that the join table has an entity of its own. Then you will have a created_on and updated_on timestamp for that relationship managed by rails.

Danzig

The SQL standard explicitly says nothing about ordering. It's usually creation order because that's how record get picked up when result sets are being assembled, but that's just a coincidence. A database that's had a lot of updates may have records all over the place. User order if it matters. I've been burned in Rails with #last returning different things for postgres. – Ssr