PHP/Mongo – Using JOIN in queries

I’ve written one post about MongoDB and PHP about creating nested set model in this database, but there is another interesting topic: using JOINs in mongo queries. Sorry, wh… what? Joining tables in non-relational database? Yes, it’s possible and we can do this to collect much more data in only one, but more complicated query. Let’s start to see how we can achieve this.

First of all: you must use MongoDB 3.2 or newer version of database. This version added this nice feature and it’s impossible to use it in ealier editions. This nice feature is $lookup operator. As an example I show PHP code sample again – of course you can use similar mongo query in other language / library or in “clean” mongo database. First, let’s say that we have collection with blog posts – it’s very often situation – with records like this:

{
    "_id" : ObjectId("bson-id"),
    "post_name" : "My great post",
    "post_content" : "There is post content",
    "post_author" : "Me",
    "post_date" : "2017-08-12 12:00:00"
}

Now see second collection record example with post comment. Yes, I know, we can store all comments in posts collection, inside post and in array, but it’s only an example – similar to tables in relational database. We can do this in many ways, it’s only one of them:

{
    "_id" : ObjectId("bson-for-comment"),
    "comment_author" : "Guest",
    "comment_content" : "Great work!",
    "comment_date" : "2017-08-12 13:00:00",
    "comment_post" : ObjectId("bson-for-post")
}

How can we get post (or many posts) with all related comments? Without JOINs we must first get post/posts, and then get comments using comment_post $in filter or – but it isn’t great idead – loop all posts and get comments only for one in each loop step. With MongoDB 3.2 we ca use $lookup to make JOIN query, aggregate all data and get it with only one query. Here is a sample code:

$aggregate = array(
    array(
        '$match' => $filters, 
    ),
    array(
        '$lookup' => array(
            'from' => 'comments',
            'localField' => '_id',
            'foreignField' => 'comment_post',
            'as' => 'post_comments'
        ),
    ),
    array(
        '$limit' => // Example option
    ),
    array(
        '$sort' => // Other example option
    )
);

$results = $this->getCollection()->aggregate($aggregate);

It isn’t standard search with filters and additional options, but aggregate command – new PHP MongoDB library support that command and we can use it to build advanced queries. We can still use search filters, but should put them into one array and use in $match. Then is $lookup – we code is clear: we must select second collection to JOIN, local and foreign “keys”. Additional setting is “as” – it’s a field name to store array with found related objects. We can also extend aggregation and use other options for limit results, skip, sort etc. What will the result of that query? It’s only example:

{
    "_id" : ObjectId("1234567890ABCDEF"),
    "post_name" : "My great post",
    "post_content" : "There is post content",
    "post_author" : "Me",
    "post_date" : "2017-08-12 12:00:00",
    "post_comments" : [ 
        {
            "_id" : ObjectId("123456789012345"),
            "comment_author" : "Guest",
            "comment_content" : "Great work!",
            "comment_date" : "2017-08-12 13:00:00",
            "comment_post" : ObjectId("1234567890ABCDEF"),
        },
        {
            "_id" : ObjectId("123456789012346"),
            "comment_author" : "Guest 2",
            "comment_content" : "Yep!",
            "comment_date" : "2017-08-12 14:00:00",
            "comment_post" : ObjectId("1234567890ABCDEF"),
        }
    ],
}

Now we ca use all comment for post without additional queries. It can help us in many situations when we use mongodb in similar way to relational database.