Search for a command to run...
TLDR: use qualifyColumn() and qualifyColumns()
I am a computer science and full stack enthusiast obsessed with building applications for the modern web. I'm here to write about the things I encounter as a full stack developer.
I've been building web apps since ~2001, but only decided recently in the beginning of 2023 that I want to start writing. So here it goes!
No comments yet. Be the first to comment.
In this article, I share a simple helper to improve the developer experience when working with time ranges in Laravel. You will be able to drop it into your applications as-is or modify it as needed. Note: At the time of publishing, the code in this ...
An easy security boost within minutes, for free
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).
public function scopeActive(Builder $query)
{
return $query->where($query->qualifyColumn('status'), 'active');
}
public function active()
{
return $this->where($this->qualifyColumn('status'), 'active');
}
$statusColumn = (new Customer)->qualifyColumn('status');
Customer::query()->where($statusColumn, 'active'); // yields `customers.status`
// 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!