Getting comma separated Date from sql

Need Help
1

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 a.com
115 B b.com
176 C c.com
156 D d.com

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 a.com 2019-01-07,2019-01-08
156 D d.com 2019-01-05,2019-01-06,2019-01-08

How can I get the desired result. Please Help

2

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

SELECT 
`employee`.`id`,
		`employee`.`name`,
		`employee`.`email`,
(SELECT 
		    GROUP_CONCAT(`attendance`.`date`)
		FROM `attendance`
		WHERE `employee`.`id` = `attendance`.`employee_id`
		AND `attendance`.`status` = 'Absent') AS absent_date
FROM `employee`
3

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

4

You can simply add a virtual field in the model.

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