Hello, I’ve just finished writing a query which acts as a sort of “forum” schema, each parent can have multiple children (post -> comments) however I’ve realised now that comments could act as parents themselves and have comments (post -> comment -> comments, etc). Think of it like a Twitter Feed where a main tweet can have comments, those comments can have comments, etc.
Basically, I need to adjust my query to take this into account but I’ve been stuck for a while trying to figure out if there’s a nice way to do it (think of it as a recursive iteration of posts).
My current query looks like this:
db.getCollection('posts').aggregate([
{
$match: {
$and: [
{"organization": ObjectId("5fb2c122cce3930008fb9e2e")},
{"type": {
$in: ["ASKS", "UPDATE", "WINS"]
}},
{"timestamp": {$gte: new Date("2020-11-16 22:54:33.010Z")}},
{"parent": null}
]
}
},
{
$sort: {
"timestamp": -1
}
},
{
$limit: 15
},
{
$lookup: {
from: "organizations",
localField: "organization",
foreignField: "_id",
as: "organization"
}
},
{
$unwind: "$organization"
},
{
$lookup: {
from: "posts",
let: {"postId": "$_id"},
pipeline: [
{
$match: {
$expr: {
$eq: ["$parent", "$$postId"]
}
}
},
{
$lookup: {
from: "accounts",
let: { "account_id": "$author" },
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", "$$account_id"]
}
}
}
],
as: "author"
}
},
{
$unwind: "$author"
},
{
$lookup: {
from: "organizations",
let: { "organization_id": "$organization" },
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", "$$organization_id"]
}
}
}
],
as: "organization"
}
},
{
$unwind: "$organization"
}
],
as: "comments"
}
},
{
$lookup: {
from: "accounts",
localField: "author",
foreignField: "_id",
as: "author"
}
},
{
$unwind: "$author"
},
{
$project: {
"_id": 1,
"type": 1,
"content": 1,
"visibility": 1,
"author._id": 1,
"author.name": 1,
"author.user": 1,
"organization._id": 1,
"organization.name": 1,
"organization.settings.logo": 1,
"timestamp": 1,
"comments._id": 1,
"comments.type": 1,
"comments.content": 1,
"comments.author._id": 1,
"comments.author.name": 1,
"comments.author.user": 1,
"comments.timestamp": 1,
"comments.parent": 1,
"comments.organization._id": 1,
"comments.organization.name": 1,
"comments.organization.settings.logo": 1
}
}
])
Thanks in advance!