Another way to prepend table names to columns in Eloquent queries
TLDR: use qualifyColumn() and qualifyColumns()
When writing complex queries in Eloquent, you will more than likely encounter scenarios forcing you to prefix column references with the table name to handle SQL ambiguity, especially when joins are involved. Consider the following ways you might've done so:
// Un-prefixed
Customer::query()->where('status', 'active');
// Hard-coded
Customer::query()->where('customers.status', 'active');
// Clever
$table = (new Customer)->getTable();
Customer::query()->where("{$table}.status", 'active');
There is another way to do this, through Eloquent's qualifyColumn()
and qualifyColumns()
methods, available on either model or query builder instances (the latter of which simply forwards to the underlying model).
In a query scope:
public function scopeActive(Builder $query)
{
return $query->where($query->qualifyColumn('status'), 'active');
}
In a custom Eloquent builder:
public function active()
{
return $this->where($this->qualifyColumn('status'), 'active');
}
At runtime:
$statusColumn = (new Customer)->qualifyColumn('status');
Customer::query()->where($statusColumn, 'active'); // yields `customers.status`
In more complicated queries:
// This is perhaps overkill for runtime queries,
// just strictly for demonstration purposes:
$projectQry = Project::query();
$customerQry = Customer::query();
$prospectiveProjects = Project::query()
->select([
...$projectQry->qualifyColumns([
'id',
'name',
'status',
]),
...$customerQry->qualifyColumns([
'name as customer_name',
'status as customer_status',
]),
])
->leftJoin(
'customers',
$projectQry->qualifyColumn('customer_id'),
$customerQry->qualifyColumn('id')
)
->whereHas(
'customer',
fn ($q) => $q->where($q->qualifyColumn('status'), 'prospect')
)
->get();
In practice, hard-coding the table prefix is often fine, or even going un-prefixed when the query is simple for the sake of better readability. Where it becomes particularly useful is within re-usable PHP Traits, query scopes, custom Eloquent builders or packages, to maximize compatibility with consumer code.
There is nothing magical about it as you'll see under the hood in Illuminate\Database\Eloquent\Model
:
/**
* Qualify the given column name by the model's table.
*
* @param string $column
* @return string
*/
public function qualifyColumn($column)
{
if (str_contains($column, '.')) {
return $column;
}
return $this->getTable().'.'.$column;
}
It's doing exactly what you'd expect. Nevertheless, knowing that the method is available is nice, and will allow you to delegate this concern to the framework at times where appropriate.
In closing, don't go refactoring all your queries and over-engineering things unnecessarily, just keep this knowledge in your back pocket for when the usage fits!