Hello : )
I didn’t see this likes.user_id = userID the previous time.
Those i think solve the problem.
Data
(def users [{:_id 1 :name "peter"}
{:_id 2 :name "john"}
{:_id 3 :name "bob"}])
(def posts [{:_id 1 :content "post1"}
{:_id 2 :content "post2"}
{:_id 3 :content "post3"}
{:_id 4 :content "post4"}
{:_id 5 :content "post5"}])
(def likes [{:user_id 1 :post_id 1}
{:user_id 1 :post_id 2}
{:user_id 1 :post_id 3}
{:user_id 2 :post_id 1}
{:user_id 2 :post_id 4}])
Query1 (for a specific user(user_id==1),find all posts he likes(no information about posts he doesnt like))
Required indexes = likes.user_id (we need also index on posts._id but this is auto-made from mongo)
{
"aggregate": "likes",
"pipeline": [
{
"$match": {
"user_id": 1
}
},
{
"$lookup": {
"from": "posts",
"localField": "post_id",
"foreignField": "_id",
"as": "liked"
}
},
{
"$unwind": {
"path": "$liked"
}
},
{
"$replaceRoot": {
"newRoot": {
"$mergeObjects": [
"$$ROOT",
"$liked"
]
}
}
},
{
"$unset": [
"_id",
"post_id",
"liked"
]
}
],
"cursor": {},
"maxTimeMS": 1200000
}
Output
{:user_id 1, :content "post1"}
{:user_id 1, :content "post2"}
{:user_id 1, :content "post3"}
Query2(for a specific user(user_id==1),find all posts he likes and those that he doesnt(user_id will be null))
Required indexes = index on likes.user_id and likes.post_id
(this is the translation of the SQL query you wrote i think)
{
"aggregate": "posts",
"pipeline": [
{
"$lookup": {
"from": "likes",
"let": {
"posts_id": "$_id"
},
"pipeline": [
{
"$match": {
"$expr": {
"$and": [
{
"$eq": [
"$user_id",
1
]
},
{
"$eq": [
"$post_id",
"$$posts_id"
]
}
]
}
}
},
{
"$project": {
"_id": 0,
"user_id": 1
}
}
],
"as": "liked"
}
},
{
"$unwind": {
"path": "$liked",
"preserveNullAndEmptyArrays": true
}
},
{
"$project": {
"_id": 0,
"user_id": {
"$cond": [
{
"$ne": [
{
"$type": "$liked.user_id"
},
"missing"
]
},
"$liked.user_id",
null
]
},
"content": 1
}
}
],
"cursor": {},
"maxTimeMS": 1200000
}
Output
{:content "post1", :user_id 1}
{:content "post2", :user_id 1}
{:content "post3", :user_id 1}
{:content "post4", :user_id nil}
{:content "post5", :user_id nil}
The above query will be fast in MongoDB >= 5.
If you have < 5 ,replace the above $match with 2 matches.
{"$match" : {"user_id" : 1}}
{"$match" : {"$expr" : {"$eq" : ["$post_id","$$posts_id"]}}}
The first match will use the index,but the second will not use the index.
(it will be still fast,because a user cant like too many posts)
The reason is before mongodb 5, $expr couldnt use the index if it was inside a $lookup pipeline.
See this about $expr in $lookup
To avoid complex schema and joins,think also
user
likes [postid1 postid2 ...]
With a multikey index on user.likes,and if posts aren’t deleted all the time.
It can be simple and fasti think.Also a user can’t like i quess to many posts so its safe also.
Hope it helps.