I'm developing an app on Laravel 5.5 and I'm facing an issue with a specific query scope. I have the following table structure (some fields omitted):
orders
---------
id
parent_id
status
The parent_id
column references the id
from the same table. I have this query scope to filter records that don't have any children:
public function scopeNoChildren(Builder $query): Builder
{
return $query->select('orders.*')
->leftJoin('orders AS children', function ($join) {
$join->on('orders.id', '=', 'children.parent_id')
->where('children.status', self::STATUS_COMPLETED);
})
->where('children.id', null);
}
This scope works fine when used alone. However, if I try to combine it with any another condition, it throws an SQL exception:
Order::where('status', Order::STATUS_COMPLETED)
->noChildren()
->get();
Leads to this:
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous
I found two ways to avoid that error:
Solution #1: Prefix all other conditions with the table name
Doing something like this works:
Order::where('orders.status', Order::STATUS_COMPLETED)
->noChildren()
->get();
But I don't think this is a good approach since it's not clear the table name is required in case other dev or even myself try to use that scope again in the future. They'll probably end up figuring that out, but it doesn't seem a good practice.
Solution #2: Use a subquery
I can keep the ambiguous columns apart in a subquery. Still, in this case and as the table grows, the performance will degrade.
This is the strategy I'm using, though. Because it doesn't require any change to other scopes and conditions. At least not in the way I'm applying it right now.
public function scopeNoChildren(Builder $query): Builder
{
$subQueryChildren = self::select('id', 'parent_id')
->completed();
$sqlChildren = DB::raw(sprintf(
'(%s) AS children',
$subQueryChildren->toSql()
));
return $query->select('orders.*')
->leftJoin($sqlChildren, function ($join) use ($subQueryChildren) {
$join->on('orders.id', '=', 'children.parent_id')
->addBinding($subQueryChildren->getBindings());
})->where('children.id', null);
}
The perfect solution
I think that having the ability to use queries without prefixing with table name without relying on subqueries would be the perfect solution.
That's why I'm asking: Is there a way to have table name automatically added to Eloquent query methods?
from Is there a way to have table name automatically added to Eloquent query methods?
No comments:
Post a Comment