How to count objects on a recursive relation on MongoDB?-CodePudding

I've a model like this

Story:
- id

Chapter:
- id
- story_id

Comment:
- id
- chapter_id

Reply:
- id
- comment_id

I require to count how many comments are on a story, which implies to search all the chapters on a story then count all the comments pointing to each, and then search all replies for each comment and add to the number. Is there a way to do this with a single query? In SQL i would normally join all the tables, and count the results, but i'm not sure if this can be done with Mongo.

CodePudding user response：

use aggregation $lookup for each collection and then $unwind after that group by id (means story id)

https://mongoplayground.net/p/zMHas1JKjdh

db.Story.aggregate([
  {
    "$lookup": {
      "from": "Chapter",
      "localField": "id",
      "foreignField": "story_id",
      "as": "chapter"
    }
  },
  {
    "$unwind": "$chapter"
  },
  {
    "$lookup": {
      "from": "Comment",
      "localField": "chapter.id",
      "foreignField": "chapter_id",
      "as": "comment"
    }
  },
  {
    "$unwind": "$comment"
  },
  {
    "$group": {
      "_id": "$id",
      "count": {
        "$sum": 1
      }
    }
  }
])