I’ve been tasked with adding a new specific column to a report with criteria that returns a new column value based on conditions. It seems like the ideal use for a CASE clause:

select *,
CASE
  WHEN check_out_time >= now() + INTERVAL 25 HOUR THEN "Requires Attention"
  WHEN (
    SELECT count(*)
    FROM shift_activities
    WHERE shift_id = `shifts`.`id`
  ) <= 0 THEN "Requires Attention"
  ELSE "Complete"
END AS "VisitStatus"
from `shifts` 
-- JOINs left off

The only downside is, one of these CASE/WHEN clauses requires a subquery, which doesn’t really fit into Eloquent nicely. In this case, the Eloquent query is relatively simple:

$open_shifts = $client->shifts()
  ->with("customers", "activities")
  ->get();

Using the laravel-eloquent-case extension, its straightforward to add a CASE that’s a simple comparison:

$open_shifts = $client->shifts()
  ->with('caregiver', 'activities')
  ->case(function (CaseBuilder $case) {
    $case->when('checked_out_time', '>=', 'INTERVAL 25 HOUR')->then('Requires Attention')
      ->else('Complete');
    }, 'VisitStatus')
  ->get();

But since I have one that relies on a count(*) of a joining table, it doesn’t work that well. It causes persistent errors and I am not clear on how to best use that library here. What other options do I have to use this CASE/WHEN in Eloquent, or should I just use Raw SQL here?