2

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.

4
  • 1
    Care to explain what you are trying to achieve? Commented Oct 11, 2024 at 10:09
  • What happens when you try to modify the "group_concat_max"...? Have you tried this solution..? Post Commented Oct 11, 2024 at 10:33
  • 1
    I don't think you can make this query work on a shared hosting as shared hosting providers do not want the users to tweak server variables that can impact other users' experience. I also don't think you should use sql to generate formatted json output. Relational databases are designed to return tabular output, which you can transform in the application code to json. Commented Oct 11, 2024 at 10:44
  • 3
    I have tried also 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. Commented Oct 11, 2024 at 10:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.