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),
        ),
    )
);

Ravi Shankar Bharti · Accepted Answer · 2019-07-11 11:51:59Z

1

You can use aggregation pipeline for that

Use $lookup to join the two collections,
$unwind the resultant array from $lookup stage
$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.

edited Jul 11, 2019 at 11:51

answered Jul 11, 2019 at 7:53

Ravi Shankar Bharti

9,2786 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

akgola Over a year ago

,this is working fine after changing little code but '$unwind' => 'master is not working and also how to apply limit and sort option also?

Ravi Shankar Bharti Over a year ago

use '$unwind'=> '$master'. Also, for sort and limit, you can add pipeline stages as well. I will update the answer

akgola Over a year ago

everything is working fine except unwind is still not working.Please help array( "\$unwind" => "master" ),

akgola Over a year ago

,now error is not throw but result is blank when putting this code.

akgola Over a year ago

It is working now,thank you so much for quick reply and help.

|

Collectives™ on Stack Overflow

MongoDb Join query with PHP

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related