5

I am using Parse Server, which runs on MongoDB.

Let's say I have collections User and Comment and a join table of user and comment. User can like a comment, which creates a new record in a join table.

Specifically in Parse Server, join table can be defined using a 'relation' field in the collection.

Now when I want to retrieve all comments, I also need to know, whether each of them is liked by the current user. How can I do this, without doing additional queries?

You might say I could create an array field likers in Comment table and use $elemMatch, but it doesn't seem as a good idea, because potentially, there can be thousands of likes on a comment.

My idea, but I hope there could be a better solution:

I could create an array field someLikers, a relation (join table) field allLikers and a number field likesCount in Comment table. Then put first 100 likers in both someLikers and allLikers and additional likers only in the allLikers. I would always increment the likesCount.

Then when querying a list of comments, I would implement the call with $elemMatch, which would tell me whether the current user is inside someLikers. When I would get the comments, I would check whether some of the comments have likesCount > 100 AND $elemMatch returned null. If so, I would have to run another query in the join table, looking for those comments and checking (querying by) whether they are liked by the current user.

Is there a better option?

Thanks!

David Riha
  • 1,368
  • 14
  • 29
  • you don't need to create that joined table whatever, you need relations and create stats etc on the fly – Tino Costa 'El Nino' Jan 16 '18 at 20:47
  • Looks like you are you using MongoDB's API ($elemMatch) to query your data... is there a reason why you don't use Parse queries for that? – dr_barto Jan 20 '18 at 06:59
  • @dr_barto because I need to know, on Find query, whether the user is inside the likers array, without selecting the whole array. Parse doesn't seem to support this, but I haven't tried to implement it yet with the Mongo API, it's just an idea. "The $elemMatch operator limits the contents of an field from the query results to contain only the first element matching the $elemMatch condition." https://docs.mongodb.com/manual/reference/operator/projection/elemMatch/ – David Riha Jan 20 '18 at 08:19
  • Are you running the query on the backend with PHP ($elemMatch looks like that)? – dr_barto Jan 20 '18 at 09:14
  • Why are you pulling **all** the comments ? I would imagine you would want to implement some kind of pagination/limit, no ? The idea of pulling all comments at once is never going to scale well no matter what solution you use. Can you explain the use case which requires you to pull all the comments liked by a user ? – s7vr Jan 21 '18 at 19:02
  • @Veeram actually I am not pulling all of them, I do have pagination, it was just an example. – David Riha Jan 21 '18 at 19:10
  • oh okay. You can do a server [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) from comment table into join table and check if it contains user id same as current user. Something like `db.commentcol.aggregate({$lookup : {from : "relationcol", localField : "_id", foreignField : "commentid", as : "commentusers"}}, {$unwind:"$commentusers"}, {$match:{"commentusers.userid":current user id}})`. The [$lookup + $unwind + $match](https://jira.mongodb.org/browse/SERVER-21612) is optimized to use index when there is one. – s7vr Jan 21 '18 at 19:35
  • Did you try my suggestion ? – s7vr Jan 24 '18 at 12:01
  • @Veeram what would that query return? only comments liked by current user or all comments, with some 'liked' flag? I need all with the flag, and it seems to me that $elemMatch is more straightforward for that purpose. Please correct me if I'm wrong, the query you suggested is a little complicated for me, I am not much experienced with mongoDB but I can work on it if you think this would solve it :) – David Riha Jan 24 '18 at 13:16
  • This will return all the comments liked by current user but no "liked" flag added in the response. To get all comments with liked flag you can add a addFields stage to check if each comment is liked or not by comparing the userid with current user. Something like `db.commentcol.aggregate([ {"$lookup" : { "from" : "relationcol", "localField" : "_id", "foreignField" : "commentid", "as" : "commentusers" }}, {"$unwind":"$commentusers"}, {"$addFields":{liked:{$eq:["$commentusers.userid", user id]}}} ])` – s7vr Jan 24 '18 at 13:29
  • You can use `preserveNullAndEmptyArrays` option when you `$unwind` to keep the comments which are not liked by anybody as "commentusers" from `$lookup` will be empty in those cases. – s7vr Jan 24 '18 at 13:32
  • "This will return all the comments liked by current user" - it's not what I want, I want all comments (e.g. all comments under some post etc.), and know which are liked by current user and which not. – David Riha Jan 24 '18 at 13:42
  • Yeah the updated query that I suggested in last comment will do exactly what you need. Try `db.commentcol.aggregate([ {"$lookup" : { "from" : "relationcol", "localField" : "_id", "foreignField" : "commentid", "as" : "commentusers" }}, {"$unwind":"$commentusers"}, {"$addFields":{liked:{$eq:["$commentusers.userid", user id]}}} ])`.I don't know how your current query looks like. You can query the post with id followed by $lookup on comments to get all comments for that post and then the above lookup to check if current user likes the comment or not. – s7vr Jan 24 '18 at 13:53
  • Ok, thanks! I will try it, but I am not sure if it is a good idea to define the whole query on the mongodb level when I am using parse server. But it's worth trying, thanks. – David Riha Jan 24 '18 at 14:05

2 Answers2

2

Well a join collection is not really a noSQL way of thinking ;-) I don't know ParseServer, so below is just based on pure MongoDB.

What i would do is, in the Comment document use an array of ObjectId's for each user who likes the comment.

Sample document layout

{ 
    "_id" : ObjectId(""), 
    "name" : "Comment X", 
    "liked" : [
        ObjectId(""), 
        ....
    ]
}

Then use a aggregation to get the data. I asume you have the _id of the comment and you know the _id of the user.

The following aggregation returns the comment with a like count and a boolean which indicates the user liked the comment.

db.Comment.aggregate(

    [
        {
            $match: {
            _id : ObjectId("your commentId")
            }
        },
        {
            $project: {
                _id : 1,
                name :1,
                number_of_likes : {$size : "$liked"},
                user_liked: {
                            $gt: [{
                                $size: {
                                    $filter: {
                                        input: "$liked",
                                        as: "like",
                                        cond: {
                                            $eq: ["$$like", ObjectId("your userId")]
                                        }
                                    }
                                }
                            }, 0]
                        },
            }
        },
    ]
);

this returns

{ 
"_id" : ObjectId(""), 
"name" : "Comment X", 
"number_of_likes" : NumberInt(7), 
"user_liked" : true

}

Hope this is what your after.

HoefMeistert
  • 1,190
  • 8
  • 17
  • Thank you HoewMeistert. As I wrote in the question, "You might say I could create an array field likers in Comment table ... but it doesn't seem as a good idea, because potentially, there can be thousands of likes on a comment." ... Do you think that it is actually a good idea, to store the likers in an array? There is probably some size limit for each db object, right? I am afraid that hypothetically, if users give a comment few thousands likes, it would break. – David Riha Jan 20 '18 at 00:11
  • 1
    @DavidRiha sorry misser that. There is a document limit of 16MB! But you can store alot of objectids in an array before you hit that limit. And when you are afraid your document size is exceeding 16MB there are Some Solutions you can look into. Cheers! – HoefMeistert Jan 20 '18 at 07:59
  • @DavidRiha a potential Solution you could look into is the use of a “continuation” document. If you have the book “MongoDB the definitive guide” check the chapter about denormalization section “Will Wheathon effect”. If you have any question let me know! – HoefMeistert Jan 20 '18 at 09:14
2

I'd advise agains directly accessing MongoDB unless you absolutely have to; after all, the way collections and relations are built is an implementation detail of Parse and in theory could change in the future, breaking your code.

Even though you want to avoid multiple queries I suggest to do just that (depending on your platform you might even be able to run two Parse queries in parallel):

  1. The first one is the query on Comment for getting all comments you want to display; assuming you have some kind of Post for which comments can be written, the query would find all comments referencing the current post.
  2. The second query again is for on Comment, but this time
    • constrained to the comments retrieved in the first query, e.g.: containedIn("objectID", arrayOfCommentIDs)
    • and constrained to the comments having the current user in their likers relation, e.g.: equalTo("likers", currentUser)
dr_barto
  • 5,723
  • 3
  • 26
  • 47
  • Accepting for now, as it really is quite complicated to access the mongodb directly in Parse Server. But still I think implementing the $elemMatch or $lookup solution in Parse Server in the future would be the best :) – David Riha Feb 05 '18 at 18:49