Fetch employee list along with number of task completed by each

ghz 9months ago ⋅ 186 views

Fetch employee list along with number of task completed by each employee, in Laravel MongoDB

I am using Laravel MongoDB library (formerly know as Laravel Jenssegers MongoDB library).

This is my employees collection

{
    _id         : {$oid: 2ndkj3720eA83b24},
    name        : 'Employee1',
    status      : 'a',
    deleted_at  : null
}

This is my task collection:-

{
    _id              : {$oid: 893jfo2ok01398190},
    name             : 'Task 1',
    employees        : [
                         {$oid: ae2nvg6788eA83b24},
                         {$oid: be09gh56701398190},
                         {$oid: bf0e28bfi45202bc0}
                       ],
    complete_status  : 'c',
    status           : 'a',
    deleted_at       : null
}

The employees column in task collection is an array of object ids of employees.

This is how I fetch employee list:-

$employeeQuery  = Employee::where(array(
                                'status'          => Globals::SMALL_CHAR_ACTIVE,
                                'deleted_at'      => NULL
                            ));

if($employeeQuery->count() > 0) {
    $employeeListing = $employeeQuery->get()->toArray();
}

But now I have to fetch employee list along with number of completed tasks for each employee. Like this:-

{
    _id  : {$oid: 2ndkj3720eA83b24},
    name : 'Employee1',
    task_completed: 23
}

How can I do this?

Answers

To fetch the employee list along with the number of completed tasks for each employee, you can use aggregation in MongoDB. Here's how you can achieve this using Laravel MongoDB library:

use App\Models\Employee;
use Illuminate\Support\Facades\DB;

$employeeListWithTasks = Employee::raw(function ($collection) {
    return $collection->aggregate([
        [
            // Match only active employees
            '$match' => [
                'status' => 'a',
                'deleted_at' => null
            ]
        ],
        [
            // Lookup tasks that are completed
            '$lookup' => [
                'from' => 'tasks',
                'localField' => '_id',
                'foreignField' => 'employees',
                'as' => 'tasks'
            ]
        ],
        [
            // Filter completed tasks
            '$match' => [
                'tasks.complete_status' => 'c',
                'tasks.deleted_at' => null
            ]
        ],
        [
            // Group by employee and count completed tasks
            '$group' => [
                '_id' => '$_id',
                'name' => ['$first' => '$name'],
                'task_completed' => ['$sum' => 1]
            ]
        ],
        [
            // Project to select fields
            '$project' => [
                '_id' => 1,
                'name' => 1,
                'task_completed' => 1
            ]
        ]
    ]);
});

// You can now use $employeeListWithTasks which contains the list of employees with the number of completed tasks for each employee

This aggregation pipeline performs the following steps:

  1. Match active employees with a status of 'a' and deleted_at set to null.
  2. Lookup tasks associated with each employee using the $lookup stage.
  3. Filter tasks to include only completed tasks with a complete_status of 'c' and deleted_at set to null.
  4. Group by employee and count the number of completed tasks for each employee.
  5. Project to select and output the required fields.

The result will be an array of documents, each containing the _id, name, and task_completed fields for an employee. You can further process this result as needed in your application.

#redis