Getting comma separated Date from sql

I am fairly new to cakephp and I can not figure out how this should be doneLet’s say I have two table, One is employee table and other is attendance table.

Employee Table

ID Name email
111 A
115 B
176 C
156 D

and attendance table

Attendance Table

ID Employee ID Date Status
1 111 2019-01-05 Present
2 111 2019-01-06 Present
3 156 2019-01-05 Absent
4 156 2019-01-06 Absent
5 111 2019-01-07 Absent
6 111 2019-01-08 Absent
7 156 2019-01-07 Presnt
8 156 2019-01-08 Absent

I want the result to be something similar like that–

Employee Table

Employee Id Name email Absent Date
111 A 2019-01-07,2019-01-08
156 D 2019-01-05,2019-01-06,2019-01-08

How can I get the desired result. Please Help

I am guessing as to your table and column names, but you can try:

		FROM `attendance`
		WHERE `employee`.`id` = `attendance`.`employee_id`
		AND `attendance`.`status` = 'Absent') AS absent_date
FROM `employee`

Thanks but I do not want use plain sql. Is there anything that can be done with the query builder?

You can simply add a virtual field in the model.

public $availableVirtualFields = array(
‘absent’ => “SELECT
FROM attendance
WHERE = attendance.employee_id
AND attendance.status = ‘Absent’”