php - Getting Comma Separated Dates from multiple rows in SQL using cakephp

Solution:

Sounds like you don't need the query to make them comma-separated, so that's something better done in the view. Along these lines (untested):

$query = $this->Employees->find()
    ->contain(['Attendance' => [
        'queryBuilder' => function (Query $q) {
            return $q->where(['Attendance.status' => 'Absent']);
        }
    ]]);

This will get the list of employees with all their absent attendance records. (If you need to get only employees that were absent at least once, you can use ->matching(...) to do this.

Then in your view:

foreach ($query as $employee) {
    // Output $employee->id, name, email, you know how to do that
    echo implode(',', collection($employee->attendance)->extract('Date')->toArray());
}

Answer

Solution:

If you can use plain sql query, you do it like this, join employees table with attendance table and use group_concat with if function to get absence dates into a list :

SELECT emp.ID, 
       emp.Name, 
       emp.email, 
       GROUP_CONCAT(IF(att.Status = 'Absent', att.Date, NULL) SEPARATOR ',') AS `Absent Date`  
FROM   Employee emp
JOIN   Attendance att
ON     emp.ID = att.`Employee ID`
GROUP BY emp.ID, emp.Name, emp.email

Demo<>dbfiddle.uk

Source