CODEWITHSUNDEEP
javascriptnode.jsmongodbmongooseaggregation-framework
koder613
koder613

Reputation: 1576

single chained query mongodb / mongoose to fetch all comments

I am trying to develop a personal project, a website that functions in a similair way to Stack Exchange, a user can ask a question which can receive multiple answers. Each question and answer can have multiple comments.

I am using nodeJS for my backend.

How can I fetch all comments for all the answers on a particular question in a single mongoDB / mongoose query?

It would be even more helpful if you could tell me how to fetch all comments for all the answers on a particular question as well as all the comments for the question in a single mongoDB / mongoose query?

Mongoose Schemas:

const questionSchema = new mongoose.Schema({
    title: String,
    content: String
})

const answerSchema = new mongoose.Schema({
    questionId: String,
    content: String,
})

const commentSchema = new mongoose.Schema({
    idQuestion: String, // nullable
    idAnswer: String, // nullable
    content: String
})

Currently, I am performing a mongoose query to find all the answers for a particular questions. Then, using forEach, performing a mongoose query on each answer to find all the comments for each answer. I believe this is very taxing, performance wise and is not an ideal way to do what I would like to achieve.

Upvotes: 3

Views: 542

Answers (2)

turivishal
turivishal

Reputation: 36104

You can try,

  • $match your conditions questionId
  • $lookup join with comments
db.answers.aggregate([
  { $match: { questionId: 1 } },
  {
    $lookup: {
      from: "comments",
      localField: "_id",
      foreignField: "idAnswer",
      as: "comments"
    }
  }
])

Playground

Second approach, if you want to select questions with all answers and comment then try,

  • $match your conditions
  • $lookup with pipeline join with answers collection
    • pipeline field can allow to add all stages of pipeline that we are using in root level
    • $match questionId and get answers
    • $lookup join with comments collection
db.questions.aggregate([
  { $match: { _id: 1 } }, // this is optional if you want to select all questions then remove this 
  {
    $lookup: {
      from: "answers",
      let: { questionId: "$_id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$questionId", "$questionId"] } } },
        {
          $lookup: {
            from: "comments",
            localField: "_id",
            foreignField: "idAnswer",
            as: "comments"
          }
        }
      ],
      as: "answers"
    }
  }
])

Playground

Show or hide extra fields, you can use $project operator at the end of above query,

  • You can show fields as per your needs
  {
    $project: {
      _id: 1,
      content: 1,
      "comments._id": 1,
      "comments.content": 1
    }
  }

Playground

Suggestions:

I am not sure, you have already done or not, but try to define object id type in reference field instead of string type, like I have updated your schema, this will add a default index in object id and this will increase a speed of fetching data,

const answerSchema = new mongoose.Schema({
    questionId: mongoose.Types.ObjectId,
    content: String,
})

const commentSchema = new mongoose.Schema({
    idQuestion: mongoose.Types.ObjectId, // nullable
    idAnswer: mongoose.Types.ObjectId, // nullable
    content: String
})

Upvotes: 1

s7vr
s7vr

Reputation: 75924

You can try below aggregation. Match on question id followed by join to lookup all the answers ids with question id followed by lookup to pull in all comments.

db.questions.aggregate([
  {"$match":{"_id":input_question_id}},
  {"$lookup":{
    "from":"answers",
    "localField":"_id",
    "foreignField":"questionId",
    "as":"answers"
  }},
  {"$lookup":{
    "from":"comments",
    "let":{"ids":{"answers_id":"$answers._id","question_id":"$_id"}},
    "pipeline":[
      {"$match":{"$expr":{
        "$or":[
          {"$eq":["$idQuestion","$$ids.question_id"]},
          {"$in":["$idAnswer","$$ids.answers_id"]}
        ]
      }}}
    ],
    "as":"comments"
  }},
  {"$project":{"comments":"$comments.content"}}
])

Working example here - https://mongoplayground.net/p/qBlKqk-JsxA

Upvotes: 1

Related Questions