CODEWITHSUNDEEP
mongodbmongoid
Geraud Mathe
Geraud Mathe

Reputation: 711

count hasMany relation in mongodb/mongoid

enter code hereI"m currently working on a rails app running mongodb via mongoid. Let's say I have two collections , posts and comments, and they're linked by a HABTM relation, I'd like to select the posts that have the most comments, and I think the way to do it is to count the comments for each post and order by comments.count DESC. I don't know how to count the number of comments for each posts, add a column to the posts results like count(comments) AS comments_count in SQL, and order by this "pseudo-field".

Any thoughts on this ?

Thanks

EDIT, looking at the others answers related to this, I've tried:

db.posts.aggregate([  
  { $group: {  
    _id: { post_id: '$post_id', comment_id: '$comment_id' }  
  }},  
  { $group: {  
    _id: '$_id.post_id',  
    comments_count: { $sum: 1 }  
  }}]  
  , function(err, result){  
     console.log(result);  
  }  
);

I'm getting

{
"result" : [{
        "_id" : null,
        "datasets_count" : 1
    }],
"ok" : 1
}

Upvotes: 1

Views: 513

Answers (1)

Gary Murakami
Gary Murakami

Reputation: 3402

The aggregation framework in MongoDB has operators for grouping, summing, and sorting. The following is a working example of how to group, count, and sort posts by the number of comments. Hope that this is what you want and that it helps.

models/post.rb

class Post
  include Mongoid::Document
  has_many :comments
  field :text, type: String
end

models/comment.rb

class Comment
  include Mongoid::Document
  belongs_to :post
  field :text, type: String
end

test/unit/post_test.rb

require 'test_helper'
require 'pp'
class PostTest < ActiveSupport::TestCase
  def setup
    Post.delete_all
    Comment.delete_all
  end
  test "posts ordered by comment count" do
    [ [ "Now is the time for all good men to come to the aid of their country.",
        [ "Tally ho!" ] ],
      [ "Twas brillig, and the slithy toves did gyre and gimble in the wabe.",
        [ "Off with their heads!",
          "The time has come, the walrus said.",
          "Curiouser and curiouser." ] ],
      [ "The quick brown fox jumped over the lazy dog.",
        [ "The typewriter is mightier than the sword.",
          "Dachshund is a badger dog." ] ]
    ].each do |post, comments|
      post = Post.create(text: post)
      comments.each{|comment| post.comments << Comment.create(text: comment)}
    end
    pipeline = [
        { '$group' => {
            '_id' => '$post_id',
            'count' => { '$sum' => 1 }
          }
        },
        { '$sort' => { 'count' => -1}
        }
    ]
    result = Comment.collection.aggregate(pipeline).to_a.collect do |post_id_count|
      Post.find(post_id_count['_id'])
    end
    puts
    pp result
  end
end

rake test

Running tests:

[1/1] PostTest#test_posts_ordered_by_comment_count
[#<Post _id: 52717e7f7f11ba52d8000003, text: "Twas brillig, and the slithy toves did gyre and gimble in the wabe.">,
 #<Post _id: 52717e7f7f11ba52d8000007, text: "The quick brown fox jumped over the lazy dog.">,
 #<Post _id: 52717e7f7f11ba52d8000001, text: "Now is the time for all good men to come to the aid of their country.">]
Finished tests in 0.050494s, 19.8043 tests/s, 0.0000 assertions/s.
1 tests, 0 assertions, 0 failures, 0 errors, 0 skips

Upvotes: 3

Related Questions