Is there a way to have table name automatically added to Eloquent query methods?

Question

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?

Answer 1

I would use a relationship:

public function children() {     return $this->hasMany(self::class, 'parent_id')         ->where('status', self::STATUS_COMPLETED); }  Order::where('status', Order::STATUS_COMPLETED)     ->whereDoesntHave('children')     ->get(); 

This executes the following query:

select * from `orders` where `status` = ?   and not exists     (select *      from `orders` as `laravel_reserved_0`      where `orders`.`id` = `laravel_reserved_0`.`parent_id`        and `status` = ?) 

It uses a subquery, but it's short, simple and doesn't cause any ambiguity problems.

I don't think that performance will be a relevant issue unless you have millions of rows (I assume you don't). If the subquery performance will be a problem in the future, you can still go back to a JOIN solution. Until then, I would focus on code readability and flexibility.

A way to reuse the relationship (as pointed out by the OP):

public function children() {     return $this->hasMany(self::class, 'parent_id'); }  Order::where('status', Order::STATUS_COMPLETED)     ->whereDoesntHave('children', function ($query) {         $query->where('status', self::STATUS_COMPLETED);     })->get(); 

Or a way with two relationships:

public function completedChildren() {     return $this->children()         ->where('status', self::STATUS_COMPLETED); }  Order::where('status', Order::STATUS_COMPLETED)     ->whereDoesntHave('completedChildren')     ->get(); 

Answer 2

In MySQL there are two good ways to find the leaf nodes (rows) in an adjacency list. One is the LEFT-JOIN-WHERE-NULL method (antijoin), which is what you did. The other is a NOT EXISTS subquery. Both methods should have a comparable performance (in theory they do exactly the same). However the subquery solution will not introduce new columns to the result.

return $query->select('orders.*')     ->whereRaw("not exists (         select *         from orders as children         where children.parent_id = orders.id           and children.status = ?     )", [self::STATUS_COMPLETED]);