$lookup with php mongodb

Question

I have a Student Table in mongo db like

      {
       "_id": ObjectId("5baa85f61d7859401000002a"),
        "Name": "Bella Dave",
         "RollNo": 12,
        "Class": "Ist",
        "TransportDetails": [
         {
            "RouteId": ObjectId("5baa93a21d7859401000002b"),
            "StopId": "abc123",
            "Status": "Inactive" 
         },
         {
            "RouteId": ObjectId("5baa818d1d78594010000029"),
            "StopId": "abc456",
            "Status": "Active" 
         } 
       ] 
     }

I have Route Table like

  {
     "Name": "New york City",
     "StopDetails": [
      {
        "StopId": "abc123",
        "Name": "Block no 1"
      },
     {
       "StopId": "abc567",
       "Name": "Block no 2"
     }
   ]

I have written below aggregation query like

       $cursor = $this->db->TblStudent->aggregate([
    [
        '$addFields' => [
            'ActiveRouteId' => [
                '$map' => [
                    'input' => '$TransportDetails',
                    'as' => 'item',
                    'in' => [
                        '$cond' => [
                            ['$eq' => ['$$item.Status', "Active"]],
                            '$$item.RouteId',
                            false
                        ]
                    ]
                ]
            ]
        ]
    ],
     [
        '$addFields' => [
            'ActiveStopId' => [
                '$map' => [
                    'input' => '$TransportDetails',
                    'as' => 'item',
                    'in' => [
                        '$cond' => [
                            ['$eq' => ['$$item.Status', "Active"]],
                            '$$item.StopId',
                            false
                        ]
                    ]
                ]
            ]
        ]
    ],
     array(
            '$lookup' => array(
                'from' => 'TblRoute',
                'localField' => 'ActiveRouteId',
                'foreignField' => '_id',
                'as' => 'RouteDetails'
            )
        ),
     array(
            '$lookup' => array(
                'from' => 'TblRoute',
                'localField' => 'ActiveStopId',
                'foreignField' => 'StopDetails.StopId',
                'as' => 'StopDetails'
            )
        ),
     ])->toArray();

    return $cursor;

Basically, I have to get active Route and Stop information along with student data. So, I successfully fetched ActiveRouteId and ActiveStopId using $addFields and $map operators. Based on ActiveRouteId, I am doing $lookup for fetching active route information. I am successfully getting that in "RouteDetails" embedded document. Now I issue is in the line

            array(
            '$lookup' => array(
                'from' => 'TblRoute',
                'localField' => 'ActiveStopId',
                'foreignField' => 'StopDetails.StopId',
                'as' => 'StopDetails'
            )
        ),

This lookup is not fetching anything. Please help!!!

Is it possible to find route and stop information together. I mean in route table there are many other embedded documents, if it is possible to fetch the required embedded document like

      RouteDetails: [      
     "Name": "New york City",
     "StopDetails": [
      {
        "StopId": "abc123",
        "Name": "Block no 1"
      }
     ]

Thanks for being ready to help!!! Mongodb forum here is not responding to any problem — Nida Amin, Sep 26 '18 at 11:42
None of the data were matching the condition { "$match": { "$expr": { "$in": ["$StopDetails.StopId", "$$activeStopId"] }}}, ], — Nida Amin, Oct 01 '18 at 05:13

Ashh · Accepted Answer · 2018-09-26T20:10:39.050

2

You can try below aggregation

TblStudent.aggregate([
  [ "$addFields"=> [
    "TransportDetails"=> [
      "$cond"=> [
        "if"=> [
          "$ne"=> [ [ "$type"=> "$TransportDetails" ], "array" ]
        ],
        "then"=> [],
        "else"=> "$TransportDetails"
      ]
    ]
  ]],
  [ "$addFields"=> [
    "ActiveRouteId"=> [
      "$filter"=> [
        "input"=> "$TransportDetails",
        "as"=> "item",
        "cond"=> [ "$eq"=> ["$$item.Status", "Active"] ]
      ]
    ]
  ]],
  [ "$lookup"=> [
    "from"=> "TblRoute",
    "let"=> [ "activeRouteId"=> "$ActiveRouteId.RouteId" ],
    "pipeline"=> [
      [ "$match"=> [ "$expr"=> [ "$in"=> ["$_id", "$$activeRouteId"] ]]]
    ],
    "as"=> "RouteDetails"
  ]],
  [ "$lookup"=> [
    "from"=> "TblRoute",
    "let"=> [ "activeStopId"=> "$ActiveRouteId.StopId" ],
    "pipeline"=> [
      [ "$unwind"=> "$StopDetails" ],
      [ "$match"=> [ "$expr"=> [ "$in"=> ["$StopDetails.StopId", "$$activeStopId"] ]]],
    ],
    "as"=> "StopDetails"
  ]]
])

Similar to javascript

TblStudent.aggregate([
  { "$addFields": {
    "TransportDetails": {
      "$cond": {
        "if": {
          "$ne": [ { "$type": "$TransportDetails" }, "array" ]
        },
        "then": [],
        "else": "$TransportDetails"
      }
    }
  }},
  { "$addFields": {
    "ActiveRouteId": {
      "$filter": {
        "input": "$TransportDetails",
        "as": "item",
        "cond": { "$eq": ["$$item.Status", "Active"] }
      }
    }
  }},
  { "$lookup": {
    "from": "TblRoute",
    "let": { "activeRouteId": "$ActiveRouteId.RouteId" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$_id", "$$activeRouteId"] }}}
    ],
    "as": "RouteDetails"
  }},
  { "$lookup": {
    "from": "TblRoute",
    "let": { "activeStopId": "$ActiveRouteId.StopId" },
    "pipeline": [
      { "$unwind": "$StopDetails" },
      { "$match": { "$expr": { "$in": ["$StopDetails.StopId", "$$activeStopId"] }}},
    ],
    "as": "StopDetails"
  }}
])

Gives me following output

/* 1 */
{
    "_id" : ObjectId("5baa85f61d7859401000002a"),
    "Name" : "Bella Dave",
    "RollNo" : 12,
    "Class" : "Ist",
    "TransportDetails" : [ 
        {
            "RouteId" : ObjectId("5baa93a21d7859401000002b"),
            "StopId" : "abc123",
            "Status" : "Inactive"
        }, 
        {
            "RouteId" : ObjectId("5baa818d1d78594010000029"),
            "StopId" : "abc456",
            "Status" : "Active"
        }
    ],
    "ActiveRouteId" : [ 
        {
            "RouteId" : ObjectId("5baa818d1d78594010000029"),
            "StopId" : "abc456",
            "Status" : "Active"
        }
    ],
    "routeDetails" : [ 
        {
            "_id" : ObjectId("5baa818d1d78594010000029"),
            "Name" : "New york City",
            "StopDetails" : [ 
                {
                    "StopId" : "abc123",
                    "Name" : "Block no 1"
                }, 
                {
                    "StopId" : "abc567",
                    "Name" : "Block no 2"
                }
            ]
        }
    ],
    "StopDetails" : [ 
        {
            "_id" : ObjectId("5baa93a21d7859401000002b"),
            "Name" : "New york City",
            "StopDetails" : {
                "StopId" : "abc456",
                "Name" : "Block no 2"
            }
        }
    ]
}

edited Sep 26 '18 at 20:10

answered Sep 26 '18 at 11:53

Ashh

44,693
14
105
132

Thanks for help but it is throwing error in the line 'pipeline' => [ telling message "Parse error: syntax error, unexpected ''pipeline'' (T_CONSTANT_ENCAPSED_STRING), expecting ')' in " please help the solution is very near – Nida Amin Sep 26 '18 at 12:00
Forgot to put `,`. Updated the answe please check – Ashh Sep 26 '18 at 12:02
Sorry again... now it is throwing another error "Fatal error: Uncaught exception 'MongoDB\Driver\Exception\RuntimeException' with message ''ActiveStopId' starts with an invalid character for a user variable name' " – Nida Amin Sep 26 '18 at 12:05
Please note that the lattest error is in vendor (driver of mongodb php) – Nida Amin Sep 26 '18 at 12:15
please wait let me check. – Ashh Sep 26 '18 at 12:16
are u able to find what is the is issue – Nida Amin Sep 26 '18 at 13:13
Hi I have updated the answer. Tested with mongo shell. please give it a try. – Ashh Sep 26 '18 at 15:40
Sorry it is again throwing the error message " Uncaught exception 'MongoDB\Driver\Exception\RuntimeException' with message ''ActiveRouteId' starts with an invalid character for a user variable name' in C:\xampp\htdocs\project_2.0\vendor\mongodb\mongodb\src\Operation\Aggregate.php" – Nida Amin Sep 26 '18 at 17:59
Again sorry it is now throwing another error message in $in line "Fatal error: Uncaught exception 'MongoDB\Driver\Exception\RuntimeException' with message '$in requires an array as a second argument, found: missing' in C:\xampp\htdocs\project_2.0\vendor\mongodb\mongodb\src\Operation\Aggregate.php:22" – Nida Amin Sep 26 '18 at 19:04
Are you using same ? Because I have tested in mongoshell. https://stackoverflow.com/questions/51642472/in-requires-an-array-as-a-second-argument-found-missing/51645014#51645014 – Ashh Sep 26 '18 at 19:15
Yes i am using exactly your code and it is throwing error message in $in line of $match. I think now the solution is very near... – Nida Amin Sep 26 '18 at 19:21
No man not possible we are passing second argument as an array in `$in` operator `["$_id", "$$activeRouteId"]`. – Ashh Sep 26 '18 at 19:24
I realised now that there are some students where TransportDetails embedded document does not exist and are empty like TransportDetails [] or TransportDetails may contain all subdocuments with the Status Inactive. I removed all such students then it run without any error message but the StopDetails is comming empty in the case Where the status is Active – Nida Amin Sep 26 '18 at 19:47
Since mongo db is schema free why it is throwing error message.... is it possible to make the above query to work in all cases. – Nida Amin Sep 26 '18 at 19:52
Added one more stage. Give it a try – Ashh Sep 26 '18 at 20:00
I am highly grateful to you for helping me and giving me so much of your time. Great help !!! Now it is working without throwing any error message... It is fetching RouteDetails successfully but StopDetails is appearing blank in all the case (also where the Status is active). – Nida Amin Sep 26 '18 at 20:20
If` `{ "$in": ["$StopDetails.StopId", "$$activeStopId"] }` will match then it will definetly return the data. I have posted the result I am getting. Have a look at these two lines `{ "$unwind": "$StopDetails" }, { "$match": { "$expr": { "$in": ["$StopDetails.StopId", "$$activeStopId"] }}},` – Ashh Sep 26 '18 at 20:25

$lookup with php mongodb

1 Answers1