Rails pg order + limit and offset strange behavior

I totally don't get what's going on here - what am I doing wrong?

irb(main):022:0> Toy.order('rating DESC').all.map(&:id)
  Toy Load (2.0ms)  SELECT "toys".* FROM "toys" ORDER BY rating DESC
=> [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99]

irb(main):029:0> Toy.order('rating DESC').limit(8).offset(0).all.map(&:id)
  Toy Load (0.8ms)  SELECT "toys".* FROM "toys" ORDER BY rating DESC LIMIT 8 OFFSET 0
=> [2, 3, 4, 5, 6, 7, 8, 1]

irb(main):029:0> Toy.order('rating DESC').limit(8).offset(8).all.map(&:id)
  Toy Load (0.8ms)  SELECT "toys".* FROM "toys" ORDER BY rating DESC LIMIT 8 OFFSET 8
=> [10, 11, 12, 13, 14, 15, 16, 1]

irb(main):029:0> Toy.order('rating DESC').limit(8).offset(16).all.map(&:id)
  Toy Load (0.8ms)  SELECT "toys".* FROM "toys" ORDER BY rating DESC LIMIT 8 OFFSET 16
=> [18, 19, 20, 21, 22, 23, 24, 1]

If I don't scope limit and offset with order everything is fine. As soon as I add ordering the first object always appears in the end no matter what offset I set. Any idea why this is happening? Thanks!

Answers


At a guess rating is not a unique key, so your sort is not stable and may return rows in a different order each time it is used. Try adding id to the sort as a secondary key to make sure that the sort order is well defined, like this:

Toy.order('rating DESC, id ASC').limit(8).offset(8).map(&:id)

Note also that you shouldn't need the .all as you can apply map directly to an AREL object without forcing it to be realised first.


Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.