Avoid enumerating column names when expanding a select statement

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

When adding extra data (e.g. a calculated column) using ORM select statements, try to avoid enumerating columns manually since it's likely, when the table structure changes, that you will forget to update it.

e.g. I had this in the PHP world:

<?php
 // Slot.php file

public function scopeWithCapacity() {
   return $query->selectRaw(
       "slots.id,
       start_time,
       capacity,
       capacity - count(appointments.slot_id) as remaining_capacity
       "
   )
}

I noticed later that a slot I instantiated was mysteriously missing its stationOperator association - i.e. $slot->stationOperator was null

When I looked at the raw data returned by this scope in PHP, the ORM record had no station_operator_id field, even though this field was in the DB and would normally be picked up the model. What had happened was that I added this station_operator_id field later and forgot to include it in the list of select columns above.

Lesson

My code should have used SELECT slots.*, extras...:

 <?php
public function scopeWithCapacity() {
   return $query->selectRaw(
       "slots.*,
       capacity - count(appointments.slot_id) as remaining_capacity
       "
   )
}