I have 3 collections, posts, comments and users, the schemas for posts and comments are below:
post schema
const PostSchema: Schema = new Schema({
author: { type: String, ref: 'User' },
postID: {type: String},
text: { type: String },
});
comment schema
const commentSchema: Schema = new Schema({
author: { type: Schema.Types.ObjectId, ref: 'User' },
parentPost: {type: Schema.Types.ObjectId, ref: 'Post'},
parentPostID: {type: String}, // Post that was commented on
});
To get the comment from each post, I do an aggregate query:
const aggregateQuery = [
{
$match:{postID:{ $in: postKeys},},
},
{
$lookup:
{
from: 'comments',
localField: '_id',
foreignField: 'parentPost',
as: 'Top_Comment',
},
},
{
$sort: {
createdAt: 1,
}
,
},
]
That query works, but I can't figure out how to populate the author property of the comment since its a ref to a user.
What I tried
I tried using pipeline, but I'm a new to aggregation so I didn't know how, any ideas and solutions would be really appreciated