I have written the below pipeline for fetching data from multiple tables by performing lookup commands. The below query was working fine till yesterday without $step20. As soon as data grew in size, the clients kept adding certificates to the students and employees, the below query started breaking throwing the error message"Total size of documents in studentTbl matching pipeline's $lookup stage exceeds 104857600 bytes". Then after doing so R & D, I found $step20 which I have added to the below pipeline, but it still throws the same error message.
$page = (int) $this->page;
$length = (int) $this->length;
$param = preg_replace('!\s+!', ' ', $this->search);
$arg = trim($param);
$dateFormat = "%m/%d/%Y";
if ($this->sessionDateFormat === "m-d-Y") {
$dateFormat = "%m/%d/%Y";
} elseif ($this->sessionDateFormat === "d-m-Y") {
$dateFormat = "%d/%m/%Y";
} elseif ($this->sessionDateFormat === "Y-m-d") {
$dateFormat = "%Y/%m/%d";
}
$sortby = $this->sortby;
switch ($sortby) {
case 0:
$sortparam = "UsedFor";
break;
case 1:
$sortparam = "Type";
break;
case 2:
$sortparam = "convertedPid";
break;
case 3:
$sortparam = "certificateNo";
break;
case 4:
$sortparam = 'insensitiveFirstName';
break;
case 5:
$sortparam = "insensitiveAssignedByCombinedFirstLastName";
break;
case 6:
$sortparam = "dateAndTime";
break;
default:
$sortparam = "dateAndTime";
break;
}
$skip = ($page);
$step1 = ['$lookup' => [
'from' => 'EmployeesTbl',
'localField' => '_id',
'foreignField' => 'CertificatesDetails.documentType',
'as' => 'employeeArray'
]];
$step2 = ['$lookup' => [
'from' => 'studentTbl',
'localField' => '_id',
'foreignField' => 'certificates_details.documentType',
'as' => 'studentArray'
]];
$step3 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
"Code" => 1.0,
"Description" => 1.0,
"schoolId" => 1.0,
"employeeArray._id" => 1.0,
"employeeArray.FirstName" => 1.0,
"employeeArray.LastName" => 1.0,
"employeeArray.EmployeeNumber" => 1.0,
"employeeArray.CertificatesDetails" => 1.0,
"studentArray._id" => 1.0,
"studentArray.first_name" => 1.0,
"studentArray.last_name" => 1.0,
"studentArray.uploadsFolder" => 1.0,
"studentArray.certificates_details" => 1.0,
"studentArray.registration_temp_perm_no" => 1.0
]
];
$step4 = array(
'$match' => [
'schoolId' => new MongoDB\BSON\ObjectID($this->schoolId),
'$or' => [
['employeeArray' => ['$not' => ['$size' => 0]]],
['studentArray' => ['$not' => ['$size' => 0]]]
]
]
);
$step5 = ['$unwind' => [
'path' => '$employeeArray',
'preserveNullAndEmptyArrays' => true
]];
$step6 = ['$unwind' => [
'path' => '$studentArray',
'preserveNullAndEmptyArrays' => true
]];
$step7 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
"Code" => 1.0,
"Description" => 1.0,
"schoolId" => 1.0,
"employeeArray" => 1.0,
"studentArray" => 1.0,
"userId" => [
'$cond' => [
'if' => [
'$eq' => ['$UsedFor', 'Employee'],
],
'then' => '$employeeArray._id',
'else' => '$studentArray._id'
]
],
"firstName" => [
'$cond' => [
'if' => [
'$eq' => ['$UsedFor', 'Employee'],
],
'then' => '$employeeArray.FirstName',
'else' => '$studentArray.first_name'
]
],
"lastName" => [
'$cond' => [
'if' => [
'$eq' => ['$UsedFor', 'Employee'],
],
'then' => '$employeeArray.LastName',
'else' => '$studentArray.last_name'
]
],
"pid" => [
'$cond' => [
'if' => [
'$eq' => ['$UsedFor', 'Employee'],
],
'then' => '$employeeArray.EmployeeNumber',
'else' => '$studentArray.registration_temp_perm_no'
]
],
"certificatesDetails" => [
'$cond' => [
'if' => [
'$eq' => ['$UsedFor', 'Employee'],
],
'then' => '$employeeArray.CertificatesDetails',
'else' => '$studentArray.certificates_details'
]
],
"uploadsFolder" => [
'$cond' => [
'if' => [
'$eq' => ['$UsedFor', 'Employee'],
],
'then' => "",
'else' => '$studentArray.uploadsFolder'
]
],
]
];
$step8 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
....
'$filter' => [
'input' => '$certificatesDetails',
'as' => 'object',
'cond' => [
'$eq' => ['$$object.documentType', '$_id'],
]
]
],
]
];
$step9 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
"Code" => 1.0,
"Description" => 1.0,
"schoolId" => 1.0,
.....
// "certificatesDetails" => 1.0,
"currentCertificateDetails" => 1.0
]
];
$step10 = ['$unwind' => [
'path' => '$currentCertificateDetails',
'preserveNullAndEmptyArrays' => true
]];
$step11 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
"Code" => 1.0,
......
.....
]
];
$step12 = ['$lookup' => [
'from' => 'userTbl',
'localField' => 'createdBy',
'foreignField' => '_id',
'as' => 'createdByArray'
]];
$step13 = array(
'$match' => [
'isActive' => true
]
);
$step14 = [
'$project' => [
"_id" => 1.0,
//"studentArray" => 1.0,
....
.....
"toShow" => 1.0,
"isActive" => 1.0,
"createdByArray" => 1.0,
"userActualId" => '$createdByArray.uid',
"certificateNo" => 1
]
];
$step15 = ['$lookup' => [
'from' => 'EmployeesTbl',
'localField' => 'userActualId',
'foreignField' => '_id',
'as' => 'userActualArray'
]];
$step16 = ['$unwind' => [
'path' => '$userActualArray',
'preserveNullAndEmptyArrays' => true
]];
$step17 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
"Code" => 1.0,
"certificateNo" => 1,
"Description" => 1.0,
"schoolId" => 1.0,
//"employeeArray" => 1.0,
//"studentArray" => 1.0,
...
"userActualId" => 1.0,
"assignedByFirstName" => '$userActualArray.FirstName',
"assignedByLastName" => '$userActualArray.LastName',
"certificateNo" => 1
]
];
$step18 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
"Code" => 1.0,
"Description" => 1.0,
"schoolId" => 1.0,
// "employeeArray" => 1.0,
// "studentArray" => 1.0,
"userId" => 1.0,
"certificateNo" => 1,
"firstName" => 1.0,
"lastName" => 1.0,
"firstLastNameCombined" => 1.0,
...
"toShow" => 1.0,
"isActive" => 1.0,
"createdByArray" => 1.0,
"userActualArray" => 1.0,
"userActualId" => 1.0,
"assignedByFirstName" => 1.0,
"assignedByLastName" => 1.0,
"assignedByCombinedFirstLastName" => array('$concat' => array('$assignedByFirstName', ' ', '$assignedByLastName')),
"certificateNo" => 1
]
];
$step19 = [
'$project' => [
"_id" => 1.0,
"UsedFor" => 1.0,
"Type" => 1.0,
....
"certificateNo" => 1
]
];
$querySearch = [
'$or' => array(
array("UsedFor" => new MongoDB\BSON\Regex($arg, 'i')),
array("Type" => new MongoDB\BSON\Regex($arg, 'i')),
array("pid" => new MongoDB\BSON\Regex($arg, 'i')),
array("certificateNo" => new MongoDB\BSON\Regex($arg, 'i')),
array("dateAndTimeString" => new MongoDB\BSON\Regex($arg, 'i')),
array("insensitiveCombinedFirstLastName" => new MongoDB\BSON\Regex($arg, 'i')),
array("insensitiveAssignedByCombinedFirstLastName" => new MongoDB\BSON\Regex($arg, 'i'))
)
];
$step20 = [
'allowDiskUse' => true
];
$searchCommand = ['$match' => $querySearch];
$sortCommand = array('$sort' => array($sortparam => ($this->sortbyparam == 'asc') ? -1 : 1));
$skipCommand = array('$skip' => $skip);
$limitCommand = array('$limit' => $length);
$cursor = iterator_to_array($this->db->certificateTypeTbl->aggregate(array($step1, $step2, $step3, $step4, $step5, $step6, $step7, $step8, $step9, $step10, $step11, $step12, $step13, $step14, $step15, $step16, $step17, $step18, $step19, $searchCommand, $sortCommand, $skipCommand, $limitCommand), $step20));
Please note that there are a lot of students in our database. (above 5000). I am not able to find any better solution. Kindly help!!!