I have the following query
SELECT
Dpn.Department_ID,
Dpn.Department_Name,
Dpn.Department_Icon,
Dph.department_info_header,
GROUP_CONCAT(Dpi.department_info_service SEPARATOR ', ') AS department_info_services,
CONCAT('[', GROUP_CONCAT(
DISTINCT CONCAT(
'{\"Doctor_ID\":', Dc.Doctor_ID,
',\"Doctor_FName\":\"', Dc.Doctor_FName,
'\",\"Doctor_LName\":\"', Dc.Doctor_LName,
'\",\"specialization\":\"', Dc.specialization,
'\",\"doctor_img\":\"', Dc.doctor_img,
'\",\"appointment_fee\":', Dc.appointment_fee,
',\"is_part_time\":', Dc.is_part_time,
',\"votes\":', Dc.votes,
',\"bio\":\"', IFNULL(Dc.bio, ''), '\"',
',\"patient_served\":', IFNULL(PatientCount.count, 0),
',\"schedules\":', IFNULL(Schedules.schedule_json,
'[{\"day_of_week\":\"Monday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"},' ||
'{\"day_of_week\":\"Tuesday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"},' ||
'{\"day_of_week\":\"Wednesday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"},' ||
'{\"day_of_week\":\"Thursday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"},' ||
'{\"day_of_week\":\"Friday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"},' ||
'{\"day_of_week\":\"Saturday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"},' ||
'{\"day_of_week\":\"Sunday\",\"start_time\":\"08:00 AM\",\"end_time\":\"06:00 PM\"}]'
), '}'
)
), ']') AS Doctors
FROM
Department Dpn
JOIN
DepartmentInfoHeader Dph ON Dpn.Department_ID = Dph.Department_ID
JOIN
DepartmentInfo Dpi ON Dpn.Department_ID = Dpi.Department_ID
LEFT JOIN
Doctor Dc ON Dpn.Department_ID = Dc.Department_ID
LEFT JOIN (
SELECT
Doctor_ID,
CONCAT('[', GROUP_CONCAT(
DISTINCT CONCAT(
'{\"day_of_week\":\"', day_of_week,
'\",\"start_time\":\"', DATE_FORMAT(start_time, '%r'),
'\",\"end_time\":\"', DATE_FORMAT(end_time, '%r'), '\"}'
)
ORDER BY
CASE day_of_week
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 7
END
SEPARATOR ', '), ']') AS schedule_json
FROM
DoctorSchedules
GROUP BY
Doctor_ID
) Schedules ON Dc.Doctor_ID = Schedules.Doctor_ID
LEFT JOIN (
SELECT
Doctor_ID,
COUNT(*) AS count
FROM
Patient_Results
GROUP BY
Doctor_ID
) PatientCount ON Dc.Doctor_ID = PatientCount.Doctor_ID
GROUP BY
Dpn.Department_ID, Dpn.Department_Name, Dph.department_info_header;
They are working just fine but the problem is am using shared hosting and I can not execute SET GLOBAL group_concat_max_len = 1000000; the current length of group concat is 1024 only, any suggestion to fix that?
I have tried also SET SESSION group_concat_max_len = 1000000; also doesn't work, I've tried using JSON_ARRAYAGG and JSON_OBJECT works fine but I get triple Doctors for each department.
SET SESSION group_concat_max_len = 1000000;also doesn't work Test this variable session value immediately after it is set and after your query execution. I'm afraid that you set in one connection but execute in another one.