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
parent_id
列引用同一表中的id
.我对没有任何子项的过滤记录有以下查询范围:
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]:违反完整性约束:WHERE子句中的列‘STATUS’不明确
我找到了两种方法来避免这个错误:
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.
解决方案2:使用子查询
我可以在子查询中将不明确的列分开.不过,在这种情况下,随着表的增长,性能将会降低.
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);
}
完美的解决方案
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?