2

I have a modeling like this

class Room
  include Mongoid::Document
  field :name, type: String
  has_many :messages
end

class Message
  include Mongoid::Document
  field :content, type: String
  belongs_to :room
end

I need to found the top 3 rooms that had most messages in the last 24 hours, but I have no idea from where to start.
maybe something with map/reduce?

Luiz E.
  • 6,769
  • 10
  • 58
  • 98

3 Answers3

2

Try this using mongoid aggregation

Room.collection.aggregate(
  {
    "$match" => {"$messages.created_at" => {"$gte" => 1.day.ago}},
    "$group" => { 
      _id: '$messages', count: {"$sum" => 1}
    },
    { "$sort" => { count: -1 } }
  }
)
pkrawat1
  • 671
  • 7
  • 18
  • I'm seeing ` syntax error, unexpected ':', expecting => { $sort: { count: -1 } }` – Luiz E. Dec 23 '14 at 17:20
  • @LuizE. You can't use the JavaScript style "symbol as a hash key" notation with `:$sort`. Either use a string key (`'$sort' => { count: -1 }`) or a normal symbol-and-hashrocket (`:$sort => { :count => -1 }`). This is a pretty common issue with MongoDB BTW and one of the reasons that I don't bother with the JavaScript style notation at all. – mu is too short Dec 23 '14 at 17:32
  • Just give it a try once http://docs.mongodb.org/manual/meta/aggregation-quick-reference/ – pkrawat1 Dec 23 '14 at 17:44
  • @muistooshort now I'm getting `unexpected '\n', expecting =>` – Luiz E. Dec 23 '14 at 18:07
2

I solved with this

match = { "$match" => { "created_at" => { "$gte" => 1.day.ago } } }
group = { "$group" => { _id: '$room_id', count: {"$sum" => 1 } } }
sort = { "$sort" => { count: -1 } }
limit = { "$limit" => 3 }

Message.collection.aggregate([match, group, sort, limit])
Luiz E.
  • 6,769
  • 10
  • 58
  • 98
-1

There's definitely a better way to do this, but I think this code should work:

Room.select("rooms.*, count(messages) as count").joins(:messages).where("messages.created_at < ?", 1.day.ago).group("rooms.id").order("count DESC").limit(3)
Peter R
  • 3,185
  • 23
  • 43
  • I can follow your idea, but I'm using mongoid, not activerecord hahahahaha – Luiz E. Dec 23 '14 at 14:04
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – Uyghur Lives Matter Dec 24 '14 at 13:38
  • @cpburnz It does provide an answer to the question...its just the wrong answer. Whoever flagged this as low quality was incorrect, and we should technically vote "looks ok" and downvote if necessary. – Paul Richter Dec 24 '14 at 14:52