Заявление CASE в MySQL, постоянно проходящее через первый клауз, независимо от значения.

Вопрос или проблема

Я дополнительно испортил оператор Eloquent, который требует добавления нового поля. Я реализовал это, добавив дополнительный Select Raw, который использует CASE…WHEN…ELSE для добавления этого нового поля на основе следующих правил:

Если у счета-фактуры есть связывающая запись в пункте счета (таблица имеет много записей, которые указывают, что смена связана с счетом-фактурой), независимо от любых предыдущих критериев, мы должны считать смену завершенной; это возвращает значение VisitStatus как “Завершено”
Если смена имеет время начала/окончания более 24 часов, значение VisitStatus должно быть установлено на “Превышает максимальное”
Если у смены нет зарегистрированных действий (Действия и Смены связаны через имеющиеся многие Действия Смены), то значение VisitStatus должно быть установлено на “Нет действий”
В противном случае VisitStatus устанавливается как “Завершено”

В рамках усилий по устранению нерешенных проблем с этим было внесено изменение, в результате которого некоторые из предыдущих записей возвращали неправильный статус. Я внес изменения в SQL (показано ниже), и теперь по какой-то причине все операторы выбрасываются при первом WHEN…THEN.

SELECT `shifts`.*, CASE
    WHEN (
        SELECT count(id) 
        FROM invoice_items 
        WHERE shift_id = `invoice_items`.`shift_id`) > 0
        THEN CONCAT("Завершено")
    WHEN (TIMESTAMPDIFF(second, checked_in_time, COALESCE(checked_out_time, now())) > 86400)
        THEN "Превышает максимальное"
    WHEN (
        SELECT count(*)
        FROM shift_activities
        WHERE shift_id = `shifts`.`id`
    ) <= 0 THEN "Нет действий"
    ELSE "Завершено (финальное)"
    END AS "VisitStatus"
FROM `shifts` 
WHERE `shifts`.`client_id` = 110 
    AND `shifts`.`client_id` is not null 
    AND `shifts`.`deleted_at` is null 
    AND `revised` = 0

Мне интересно, что могло пойти не так, чтобы вызвать это резкое изменение, особенно поскольку одна из неудавшихся записей не является частью счета-фактуры, но возвращает “Завершено”. Есть ли что-то, что может отсутствовать в этом, что могло бы привести к сбросу этой записи при первом WHEN…THEN?

Ответ или решение

В данной ситуации, когда SQL-запрос возвращает неверные значения VisitStatus, необходимо обратить внимание на несколько моментов, которые могли бы привести к некорректному поведению вашего выражения CASE. Давайте разберем сам запрос и выявим возможные причины:

Проверка условий в CASE:
- Убедитесь, что подзапрос в первом условии работает корректно. Он использует count(id), и результат может быть больше 0, даже если ваш shift на самом деле не связан с записью в invoice_items. Проверьте, правильно ли указаны условия соединения между таблицами.
- Убедитесь, что подзапросы не возвращают неожиданные результаты. Чтобы протестировать, выполните только подзапросы отдельно и посмотрите, чего они вам действительно возвращают.
Логику условий:
- В условии WHEN (TIMESTAMPDIFF(second, checked_in_time, COALESCE(checked_out_time, now())) > 86400) стоит проверить, что переменные checked_in_time и checked_out_time всегда корректно задаются. Если checked_out_time отсутствует, то COALESCE должен вернуться к текущему времени.
- Для условия WHEN (SELECT count(*) FROM shift_activities WHERE shift_id = shifts.id) <= 0 убедитесь, что там действительно нет связанных записей. Возможно, вам нужно задать более строгие условия.
Порядок выполнения условий:
- Убедитесь, что порядок ваших условий в CASE соответствует логике обработки: WHEN проходит с первого до последнего, и если первое условие срабатывает, последующие уже не будут проверены. Если у вас есть случаи, когда запись может подпадать под несколько условий, следите за их порядком.
Отладка запроса:
- Попробуйте временно заменить первый WHEN на более детализированное условие, которое будет возвращать не только "Complete", но и также показывать, почему оно было выбрано. Например, добавьте отладочный вывод с количеством документов, которые у вас есть, что могло бы помочь вам лучше понять, что происходит.

Вот переработанный фрагмент вашего SQL-запроса с отладочным выводом:

SELECT `shifts`.*, CASE
    WHEN (
        SELECT count(id) 
        FROM invoice_items 
        WHERE shift_id = `shifts`.`id`
    ) > 0
    THEN CONCAT("Complete (from InvoiceItems)")
    WHEN (TIMESTAMPDIFF(second, checked_in_time, COALESCE(checked_out_time, now())) > 86400)
    THEN "Exceeds Maximum"
    WHEN (
        SELECT count(*)
        FROM shift_activities
        WHERE shift_id = `shifts`.`id`
    ) <= 0 THEN "No Activities"
    ELSE "Complete (final)"
END AS "VisitStatus"
FROM `shifts` 
WHERE `shifts`.`client_id` = 110 
    AND `shifts`.`client_id` is not null 
    AND `shifts`.`deleted_at` is null 
    AND `revised` = 0

Этот запрос добавляет уточнение к тому, откуда выводится статус "Complete" в случае первого условия.

Если после выполнения всех проверок проблема сохранится, возможно, стоит использовать LEFT JOIN для проверки наличия записей в invoice_items и shift_activities напрямую, вместо использования подзапросов, что может улучшить производительность вашего запроса и позволить более эффективно работать с условиями.

Если у вас остались вопросы или требуется дальнейшая помощь, не стесняйтесь обращаться.