Search code examples
mongodbmongodb-queryaggregation-frameworkphp-mongodb

Handling 'or' and 'and' condition for fetching data in project


I have a collection with data like

 "Name": "....",
  ...
 "RoomStatusDetails": [
 {
   "StatusEntryId": ObjectId("5be1895dbb2c5620c4003ae0"),
   "RoomId": "41964158-0131-7b55-7781-a29084879e1a",
   "CurrentStatus": ObjectId("5bd17295d2ccda11f0007765"),
   "StartDate": ISODate("2018-11-06T05:00:00.0Z"),
   "Notes": "",
   "Discrepancy": "",
   "Waiver": "",
   "TFlight": "",
   "IsActive": "Inactive" 
},
 {
   "StatusEntryId": ObjectId("5be1896cbb2c5620c4003ae1"),
   "RoomId": "41964158-0131-7b55-7781-a29084879e1a",
   "CurrentStatus": ObjectId("5bd17295d2ccda11f0007766"),
   "StartDate": ISODate("2018-11-06T05:00:00.0Z"),
   "Notes": "",
   "Discrepancy": "",
   "Waiver": "",
   "TFlight": "",
   "IsActive": "Active" 
},
 {
   "StatusEntryId": ObjectId("5bf4e8a83250041388000edf"),
   "RoomId": "d72136ac-b295-d4ea-2d32-0f9ef26b7df1",
   "CurrentStatus": ObjectId("5bd17295d2ccda11f0007766"),
   "StartDate": ISODate("2018-11-26T23:00:00.0Z"),
   "Notes": "",
   "Discrepancy": "",
   "Waiver": "",
   "TFlight": "",
   "IsActive": "Active" 
},
 {
   "StatusEntryId": ObjectId("5bf4e8db3250041388000ee0"),
   "RoomId": "4c980185-65eb-eece-8176-7f187eed3132",
   "CurrentStatus": ObjectId("5bd17295d2ccda11f0007765"),
   "StartDate": ISODate("2018-11-18T23:00:00.0Z"),
   "Notes": "",
   "Discrepancy": "",
   "Waiver": "",
   "TFlight": "",
   "IsActive": "Active" 
},
 {
   "StatusEntryId": ObjectId("5bf4e97f3250041388000ee1"),
   "RoomId": "407225eb-2990-263b-4112-068d3ef0e1b2",
   "CurrentStatus": ObjectId("5bd17295d2ccda11f0007768"),
   "StartDate": ISODate("2018-12-05T23:00:00.0Z"),
   "Notes": "",
   "Discrepancy": "",
   "Waiver": "",
   "TFlight": "",
   "IsActive": "Active" 
} 

],

I am trying below lines of code

       $this->collection->aggregate(array(

                array(
                    '$project' => array(
                          'RoomStatusDetails' => array(
                            '$filter' => array(
                                'input' => '$RoomStatusDetails',
                                'as' => 'item',
                                'cond' => array(
                                    '$or' => [
                                      ['$eq' => ['$$item.CurrentStatus', new MongoDB\BSON\ObjectID($this->CAOutOfService)]],
                                      ['$eq' => ['$$item.CurrentStatus', new MongoDB\BSON\ObjectID($this->OutOfService)]]
                                    ],
                                  ['$eq' => ['$$item.IsActive', 'Active']]
                                )
                            )
                        ),
                        'Name' => 1,
                        'Address' => 1,
                        'NoOfFloors' => 1,
                        'Image' => 1,
                        'Bay' => 1,
                        'Approved' => 1,
                        'RoomsDetails' => 1,
                        'AllotmentsDetails' => 1,
                        //'TotalNumberOfCapacitiesArray' => 1
                    )
                ),
                ))->toArray();

I am trying to project embedded document RoomStatusDetails and I am trying to fetch only those RoomStatusDetails data where Current Status can be "5bd17295d2ccda11f0007765" or "5bd17295d2ccda11f0007766" which are present in $this->CAOutOfService or $this->OutOfService variables respectively and their IsActive is "Active".

The above code is throwing error "An object representing an expression must have exactly one field: { $or: [ { $eq: [ \"$$item.CurrentStatus\", ObjectId('5bd17295d2ccda11f0007769') ] }, { $eq: [ \"$$item.CurrentStatus\", ObjectId('5bd17295d2ccda11f0007766') ] } ], 0: { $eq: [ \"$$item.IsActive\", \"Active\" ] } }"

Please help!!!


Solution

  • You can build your query like this

    db.col.aggregate([{
            "$project": {
                "RoomStatusDetails": {
                    "$filter": {
                        "input": "$RoomStatusDetails",
                        "as": "stat",
                        cond: {
                            "$and": [{
                                    $or: [{
                                            $eq: ["$$stat.CurrentStatus", ObjectId("5bd17295d2ccda11f0007766")]
                                        },
                                        {
                                            $eq: ["$$stat.CurrentStatus", ObjectId("5bd17295d2ccda11f0007765")]
                                        }
                                    ]
                                },
                                {
                                    $eq: ["$$stat.IsActive", "Active"]
                                }
                            ]
                        }
                    }
                }
            }
        }
    
    ])