CODEWITHSUNDEEP
ruby-on-railspostgresqlminitest
cancue
cancue

Reputation: 414

Rails query gives different ordering during testing

My query works properly on the rails console and the web server where it returns a DESC order but fails during testing where it returns ASC order.

Below is the simplified model code.

class Room < ApplicationRecord
 has_many :room_members
 has_many :members, through: :room_members, source: :user

 def self.hot_rooms
  query = "SELECT r.*, rm,count
           FROM rooms r
           LEFT JOIN (SELECT room_id, count(room_id), max(id) as id
                      FROM room_members
                      GROUP BY room_id) rm
           ON r.id = rm.room_id
           WHERE r.is_started = true
           ORDER BY rm.count DESC, rm.id DESC
           LIMIT 12"
  self.find_by_sql(query)
 end
end

The problem is rm.count DESC.
The result of Room.recent_rooms is DESC on console and web server but surprisingly ASC when testing.

Upvotes: 0

Views: 66

Answers (1)

cancue
cancue

Reputation: 414

I fixed. The problem was caused by 'nil' count.(There was no nil on console) psql count out nil instead of 0. And It assume nil as a big number I think. So I modified the code, and I solved the problem.

  1. wm.count > coalesce(wm.count,0) as count
  2. rm.count DESC > count DESC

Upvotes: 1

Related Questions