Rails app with postgresql; query with .take does not keep the order of records

Question

I have a rails 4 application and I recently switched from SQlite to Postgresql. I have the following 3 models;

class User < ActiveRecord::Base
    has_many :user_games
    has_many :games, :through => :user_games
end

class Game < ActiveRecord::Base
    has_many :user_games
    has_many :users, :through => :user_games
end

class UserGame < ActiveRecord::Base
    belongs_to :user
    belongs_to :game
end

with the records:

game
id
1 

userGame
id  user_id   game_id
1      5         1
2      3         1

user
id
1
2

I could do this with sqlite:

game = Game.Find(1)
game.users.take(2).first

which would return User with id 5.

The same code with postgres gives user with id 3.

Why?

EDIT:

I forgot to add that I need to get the users in the order they appear in the table.

So if...

game
id
1 
2

userGame
id  user_id   game_id
1      5         1
2      3         1
3      1         2
4      4         2

user
id
1
2
3
4

... I need a query that preserves the order of the users, so that game1.users.first.id = 5 and game2.users.first.id = 1

game1.users.take(2).first.id would preserve this order when I used sqlite, but with postgresql it does not.

This works, but not for the edited part that I forgot to include. Sorry about that — klonkr
– klonkr, Commented Mar 21, 2015 at 13:46

cristian · Accepted Answer · 2015-03-21 14:40:45Z

1

take has the following explanation in manual:

Take

Gives a record (or N records if a parameter is supplied) without any
implied order. The order will depend on the database implementation. If
an order is supplied it will be respected.

If you want to get the same result with Postgresql and with Sqlite specify a default order.

game.users.order('users.id DESC').first

Or since last and first method` sorts your data in Rails 4 use:

game.users.last

This will translate into the following SQL:

SELECT * FROM users WHERE "games"."id" = 1 ORDER BY "users"."id" DESC LIMIT 1

My conclusion is that you don't need to use take at all

Edit

You have:

game = Game.find(1)
users = game.users.order("user_games.id ASC").take(2)

Then, if you iterate over your users collection:

users.each do |user|
  puts user.inspect
end

you should get those users ordered as you want.

edited Mar 21, 2015 at 14:40

answered Mar 21, 2015 at 9:04

cristian

8,7443 gold badges40 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

klonkr Over a year ago

I edited my question. Your solution works, but not to the part I had not mentioned. Sorry about that

Collectives™ on Stack Overflow

Rails app with postgresql; query with .take does not keep the order of records

1 Answer 1

Take

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Take

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related