CODEWITHSUNDEEP
ruby-on-railssqlitepostgresql
klonkr
klonkr

Reputation: 13

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

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.

Upvotes: 0

Views: 84

Answers (1)

cristian
cristian

Reputation: 8744

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.

Upvotes: 1

Related Questions