count hasMany relation in mongodb/mongoid

Question

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
}

For performant aggregations it is worth having comments embedded inside posts documents or at list support their counts. Do you have anything of that? How these collections linked to each other? Could you provide documents samples? — viktortnk, Sep 24 '13 at 19:01

score 3 · Accepted Answer · answered Oct 30 '13 at 21:55

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

This is a great answer. BTW, you will have 1 query for each `Post`, the N+1 query problem. This can be mitigated with a `$lookup`. https://stackoverflow.com/a/35948843/336920 — B Seven, Jun 25 '21 at 23:58

count hasMany relation in mongodb/mongoid

1 Answers1

Running tests: