Select with sub query

Hi,
I’m working on a simple “likes” system (3 collections: user, post, like)
i’m trying to get whether a user liked that post or not (given userID)

Using SQL, it would be something like that:
SELECT post.content, (SELECT likes.id FROM likes WHERE likes.post_id = post.id AND likes.user_id = userID) AS liked FROM post
this will produce two columns, the first contains the post content and second the id of the like record if exists (userID liked the post) or null if it does not (userID didn’t like the post)

how can i get similar results using mongodb?

any help is appreciated
thank you

1 Like

Hello @s77rt,

You can try coding your query using some examples from these topics (these topics have comparisons between SQL and MongoDB querying and other terminology):

Thanks @Prasad_Saya for your reply
however i still haven’t found a solution yet, looks like i have to use join? i’m not sure how that would effect the performances

@Takis thanks a lot for the time and effort you put in this.
it’s almost what i need, but this will results in a row for each user that liked a given post
while i only need to know whether a specific user liked a specific post.

I don’t think i should embed such data as it will grow rapidly and may have to hold a lot of data. (in liked-from array)

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.

@Takis thanks a lot
i will look into this asap and update you, thanks again for the help

thanks a lot @Takis
here is what i ended up with: (golang code)

{
	{"$lookup", bson.D{
		{"from", "likes"},
		{"let", bson.D{
			{"post", "$_id"}},
		},
		{"pipeline", bson.A{bson.D{
			{"$match", bson.D{
				{"$expr", bson.D{
					{"$and", bson.A{
						bson.D{
							{"$eq", bson.A{"$post", "$$post"}},
						},
						bson.D{
							{"$eq", bson.A{"$user", userID}},
						},
					}},
				}},
			}},
		}}},
		{"as", "helper"},
	}},
},
{
	{"$set", bson.D{
		{"liked", bson.D{
			{"$cond", bson.D{
				{"if", bson.D{{"$eq", bson.A{bson.D{{"$size", "$helper"}}, 1}}}},
				{"then", true},
				{"else", false},
			}},
		}},
	}},
},
{
	{"$unset", "helper"},
},

this will results in posts records, with an extra field “liked” which can take two values: true (if used like that post) or false (if he didn’t)