Last Updated: 2024-11-21

I had the following:

<?php

function scopeWithCapacity($query) {
    return $query->selectRaw(
      "slots.id,
      start_time,
      capacity,
      capacity - count(appointments.slot_id) as remaining_capacity"
      )
      ->leftJoin("appointments", "slots.id", "=", "appointments.slot_id")
      ->groupBy("slots.id")
      ->havingRaw("remaining_capacity > 0");
}

This failed in some SQL DBs but not others. The issue was that the having clause referenced remaining_capacity, an SQL alias.

What is happening under the hood? The select clause is usually calculated at the end, whereas having clauses have an effect much earlier in the process.

The fix in my case was to rewrite the having clause in terms of the raw columns, without reference to aliases.

<?php

function scopeWithCapacity($query) {
    return $query->selectRaw(
    "slots.id,
    start_time,
    capacity,
    capacity - count(appointments.slot_id) as remaining_capacity"
    )
    ->leftJoin("appointments", "slots.id", "=", "appointments.slot_id")
    ->groupBy("slots.id")
    ->havingRaw("capacity - count(appointments.slot_id) > 0");
}