Having clauses may not be able to use select aliases

This is part of the Semicolon&Sons Code Diary - consisting of lessons learned on the job. You're in the databases category.

Last Updated: 2022-12-01

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");
}