0

I am trying to get all rooms and order them by number of users in each room.

Each user who enters the room, stored in table roulettes, but I need rows which were added only 90 seconds ago.

This query return room ordered by number of users, but doesn't return room if the number of coresponding roulettes is zero, but I want to get all rooms.

scope :with_users_in, -> { select("rooms.id, rooms.title, rooms.creation_time, rooms.room_life, count(roulettes.id) AS listens_count").
    joins("LEFT OUTER JOIN roulettes ON roulettes.section_id = rooms.id").
    group("rooms.id").
    order("listens_count DESC").
    where("roulettes.time > (? - 90)", Time.now.to_i)
}

How can I modify my query?

For example, if rewrite my scope like this:

scope :with_users_in, -> { select("rooms.id, rooms.title, rooms.creation_time, rooms.room_life, count(roulettes.id) AS listens_count").
        joins("LEFT OUTER JOIN roulettes ON roulettes.section_id = rooms.id").
        where("roulettes.time > (? - 900000) OR roulettes.time IS NULL", Time.now.to_i).
        group("rooms.id").
        order("listens_count DESC")
    }

I get this json:

[{"id":1,"title":"fdf","creation_time":1442706728,"room_life":20000300,"url":"http://localhost:3000/rooms/1.json","listens_count":2}]

But for origin query I get []. Because last entering in room was much before than 90 seconds. And I want it to return the same thing, but instead of listens_count:2 get listens_count:0

The generated code

Room Load (0.3ms)  SELECT  rooms.id, rooms.title, rooms.creation_time,
rooms.room_life, count(roulettes.id) AS listens_count FROM "rooms" LEFT
OUTER JOIN roulettes ON roulettes.section_id = rooms.id WHERE
(roulettes.time > (1442941851 -90) OR roulettes.time IS NULL) GROUP BY
rooms.id  ORDER BY listens_count DESC LIMIT 20 OFFSET 0
user3434212
  • 87
  • 8
  • I dont know how is `on rails` but the `where` should be before the `group` – Juan Carlos Oropeza Sep 22 '15 at 16:43
  • where before group didn't help – user3434212 Sep 22 '15 at 16:47
  • Can you show us some sample data in [**SqlFiddle**](http://sqlfiddle.com/#!15/5368b/6) so we can understand the problem better and give you an answer much faster – Juan Carlos Oropeza Sep 22 '15 at 16:49
  • can you try `where("roulettes.time > (? - 90) OR roulettes.time IS NULL", Time.now.to_i)`. Also is there a default `roulettes.time` – patrickh003 Sep 22 '15 at 17:23
  • yes it is unixtimestamp, which I set on creating roulette – user3434212 Sep 22 '15 at 17:52
  • Did you change `OR roulettes.time = ?"` to `OR roulettes.time IS NULL"`? To find null values in mysql, you use `IS NULL` instead of an equal sign. – patrickh003 Sep 22 '15 at 18:57
  • yes I tried IS NULL too, but as I said roulettes.time is not null by default, it is integer – user3434212 Sep 22 '15 at 19:16
  • yes but when you `left outer join` roulettes, the rooms that don't have a roulette has null for roulette. `rooms.id: 1, rooms.title: "stuff", rooms.creation_time: 1442949977, rooms.room_life: "awesome", roulettes.id: nil, roulettes.time: nil, roulettes.section_id: nil` So the where clause eliminates these rooms without roulette cause nil is never going to be greater than time.now - 1 – patrickh003 Sep 22 '15 at 19:32
  • can you add examples of what's in the db and what is the expected output – patrickh003 Sep 22 '15 at 19:43
  • in the db, the same thing as in json in the post. I want to get the same json, but instead of listens_count:2, get listens_count:0 – user3434212 Sep 22 '15 at 19:47
  • What I am not understanding is if listens_count: 2 is the correct output and you want listen_count: 0, then use `.having("count(roulettes.id) = 0")` after the group by clause. Or listens_count: 2 is the wrong output then I will need to see data from the db. at least dummy data from both tables and expected output. – patrickh003 Sep 22 '15 at 20:23
  • no, i set difference in time 900000 instead of 90, and get old data. But for newer data it returns empty array, it just doesn't match first condition, and room is not returned. I want get all rooms ordered by listens_count. – user3434212 Sep 22 '15 at 20:26

1 Answers1

1

OK I get it. You have to remove the where clause. Where will make the query return a blank array. So one option is you can use a CASE WHEN in sum instead of count COUNT CASE and WHEN statement in MySQL

scope :with_users_in, -> { select("rooms.id, rooms.title, rooms.creation_time, rooms.room_life, SUM(CASE WHEN roulettes.time > (#{Time.now.to_i} - 900000) THEN 1 ELSE 0 END ) AS listens_count").
    joins("LEFT OUTER JOIN roulettes ON roulettes.section_id = rooms.id").
    group("rooms.id").
    order("listens_count DESC").
}
Community
  • 1
  • 1
patrickh003
  • 168
  • 1
  • 5