MongoDb Join query with PHP

Question

I have two documents and want to join both bocuments and after I want to fetch the data from merged table.

e.g below is the master document

{
    "_id" : ObjectId("5d11bab64d51f58dbbd391lm"),
    "title" : "Test test",
    "test_url" : "https://www.example.com/",

}

and this one is detail document

{
    "_id" : ObjectId("5d1360536d94f726ec484426"),
    "master_id" : ObjectId("5d11bab64d51f58dbbd391lm"),
    "title" : "जेल में असलहा लहाराते हुए बदमाशों का वीडियो वायरल, गृह विभाग ने दी सफाई",
    "description" : "जिला कारागार में अपराधियों द्वारा असलहा लहराते हुए वीडियो सामने आया है।िला कारागार में अपराधियों द्वारा असलहा लहराते हुए वीडियो सामने आया है।िला कारागार में अपराधियों द्वारा असलहा लहराते हुए वीडियो सामने आया है।"

}

both documents are linked with _id of master document and master_id. I want to join that table and want to add title in detail document.

and after result I want to add query for search the data from merged document. below is the query for search.

$queryString = ".*".$queryString.".*";
$where = array(
    '$or' => array(
        array(
            'title' => new \MongoDB\BSON\Regex($queryString),
        ),
        array(
            'description' => new \MongoDB\BSON\Regex($queryString),
        ),
    )
);

Answer 1

You can use aggregation pipeline for that

1. Use $lookup to join the two collections,
2. $unwind the resultant array from $lookup stage
3. $match to do the regex search

If you need to do the regex search on title of master document as well, you can add that to your $or query of the $match stage, and if you don't want that, don't forget to remove from the $or query (i have added it).

$pipeline = array(
    array(
        '$lookup' => array(
            'from' => 'masters',
            'localField' => '$master_id',
            'foreignField' => '$_id',
            'as' => 'master'
        )
    ),
    array(
        '$unwind' => Array(
            'path' => '$master',
            'preserveNullAndEmptyArrays' => true
         ) 
    ),
    array(
        '$match' => array(
            '$or' => array(
                array(
                    'title' => new \MongoDB\BSON\Regex($queryString),
                ),
                array(
                    'description' => new \MongoDB\BSON\Regex($queryString),
                ),
                array(
                    'master.title' => new \MongoDB\BSON\Regex($queryString),
                ),
            )
        )
    ),
    array(
        '$sort' => array(
            'field_name' => 1
        )
    ),
    array(
        '$limit' => 10
    )
)

$results = $details->aggregate($pipeline);

Now, my php is not that great, still i have managed to write the query for you. Please change/modify the code according to your need.

The thing is i gave you the idea on how to acheive this. Hope that helps.

Edit

To sort, and limit, use $sort and $limit pipeline stages, I have added that in answer.

Dont forget to replace field_name with the actual field you want to sort the result with.