Find on the basis of field of last element of embedded document in mongo db php

Question

I have a collection studentTbl which contains records like

Record 1

 "_id": ObjectId("5b45d89bbbc51e2c2c006973")
 "first_name": "Pooja",
 ...
 contact_details[
    {
     ..
    },
    {
     ..
    }
 ]
  transport_details[
    {
      allotment_id:"68998546878",
      ..
      status:"Inactive"
    },
    {
      allotment_id:"25799856890",
      ..
      status:"Active"
    }
 ]
}

Record 2

  "_id": ObjectId("5b45d89bbbc51e2c2533")
 "first_name": "Poornima",
 ...
 contact_details[
    {
     ..
    },
    {
     ..
    }
 ]
  transport_details[
    {
      allotment_id:"68998546878",
      ..
      status:"Inactive"
    }
 ]
}

Record 3

 "_id": ObjectId("5b45d89bbbc51e2c2c00646")
 "first_name": "Poonam",
 ...
 contact_details[
    {
     ..
    },
    {
     ..
    }
 ]
  transport_details[
    {
      allotment_id:"68998546878",
      ..
      status:"Inactive"
    },
    {
      allotment_id:"25799856890",
      ..
      status:"Active"
    }
  ]
}

I am trying to tweak the below lines of code in order to fetch those students whose first_name or middle_name or last_name contains "poo" and inside the last element of embedded document transport_details, status should be "Active". How to write such a query which will find students on the name basis and traverse through the last element of embedded document transport_details and checks whether the status is active? For e.g in the above collection, pooja and poonam should be returned.

The code is like

      // default if nothing is filled
      $query = array("schoolId"=>  new MongoDB\BSON\ObjectID($this->schoolId));

     // if name = filled, class = null, year = null 
     if(!empty($this->name) && empty($this->academicYearName) && empty($this->programId))
     {

           $param = preg_replace('!\s+!', ' ', $this->name);
           $arg = trim($param);

           $query =  array(
                   '$or' => array(
                                    array("first_name" => new MongoDB\BSON\Regex($arg, 'i')),
                                    array("middle_name" => new MongoDB\BSON\Regex($arg, 'i')),
                                    array("last_name" => new MongoDB\BSON\Regex($arg, 'i')),
                                    array("registration_temp_perm_no" => $arg)
                                  ),

                        "schoolId"=>  new MongoDB\BSON\ObjectID($this->schoolId)
                    );

     }
    ...
    ...
    ...
     $pipeline = array(
        array(
            '$match' => $query
        ),
        array(
            '$lookup' => array(
                'from' => 'programTbl',
                'localField' => 'classId',
                'foreignField' => '_id',
                'as' => 'ClassDetails'
            )
        ),
     );

    try
    {
        $cursor = $this->collection->aggregate($pipeline);
    } 
    catch (Exception $e) {

    }

    return $cursor->toArray();

Note that the actual code contains more conditional $query variable...

Answer 1

You can add below stages in your pipeline

array(
  "$match" => array(
    "$expr" => array(
      "$and" => [
        array(
          "$or" => [
            array( "$eq" => [ array( "$strcasecmp" => [ "$first_name", "Poo" ] ), 1 ] ),
            array( "$eq" => [ array( "$strcasecmp" => [ "$last_name", "Poo" ] ), 1 ] ),
            array( "$eq" => [ array( "$strcasecmp" => [ "$middle_name", "Poo" ] ), 1 ] ),
          ],
        ),
        array(
          "$eq" => [
            array( "$arrayElemAt" => [ array( "$slice"=> [ "$transport_details.status", -1 ] ), 0 ] ),
            "Active"
          ]
        )
      ]
    )
  )
)

Answer 2

You can use below query in 3.6.

array(
    '$or' => array(
        array("first_name" => new MongoDB\BSON\Regex("/poo/i")),
        array("middle_name" => new MongoDB\BSON\Regex("/poo/i")),
        array("last_name" => new MongoDB\BSON\Regex("/poo/i"))
    ),
    "$expr" => array(
        "$eq" => array(
            array("$arrayElemAt" => array("$transport_details.status", -1)),
            "Active"
        )
    )
);