[5.8] Query builder Increment/Decrement method when using table alias throws unknown column error for timestamps

[Adam-78]

• Laravel Version: 5.8.14
• PHP Version: 7.1.18
• Database Driver & Version: MySQL 5.7.17

Description:

When using the query builder increment or decrement method, if you use table aliases then the updated_at timestamp is prefixed with the fully qualified table name and not the alias and as such throws unknown column error.

Steps To Reproduce:

Define a query using table alias e.g.

return  $this->model->from('order_item_product as t1')
        ->join(DB::Raw('(
           select t1.product_id as product_id
                , MIN(t1.created_at) as created_at
	            , MIN(t1.order_item_id) as order_item_id
	         FROM order_item_product t1
      INNER JOIN order_items t2 on t1.order_item_id = t2.id
      INNER JOIN orders t3 on t2.order_id = t3.id
      INNER JOIN recruiters t4 on t3.recruiter_id = t4.id
           WHERE t1.product_id = ?
             AND t1.available > 0
             AND TIMESTAMPADD(DAY,t1.valid_days,t1.created_at) > now()
             AND t4.company_id = ?
        GROUP BY t1.product_id) AS t2'), function($join)
        {
            $join->on('t1.order_item_id', '=', 't2.order_item_id');
            $join->on('t1.created_at', '=', 't2.created_at');

        })
        ->setBindings([$productId, $companyId])
        ->decrement('t1.available', 1, ['updated_at' => DB::Raw('now()')]);

Run and execute query, check query log to see what the query was executed as and you will note the updated_at column has ended up being prefixed with the table name (in the above example it's ended up as order_item_product.updated_at and as such throws unknown column error because the table was aliased in the query as t1.

If you omit the updated_at column from the query e.g. ->decrement('t1.available', 1); it still gets added to the query as order_item_product.updated_at and messes up the order of bindings.

If you change the above example query by prefixing the updated_at column with the table alias e.g. ['t1.updated_at' => DB::Raw('now()')] , the same thing happens i.e. an additional order_item_product.updated_at timestamp gets added to the query and with the bindings messed up and unknown column error.

A similar effect occurs when using queryscopes where multiple tables are joined. In this scenario no table prefix is applied and if there's a column that has the same name in different tables you get the same ambiguous column error.

For example if I have the following query scope in my Article model and use it with a query join to another table that also has a status_id field I get the ambiguous column error for status_id:

 public function scopePublished($query)
 {
     return $query->where('start_at', '<=', Carbon::now())
          ->where('end_at', '>=', Carbon::now())
          ->where('status_id', '=', 4);
 }

 Article::join('authors', 'authors.id', '=', 'articles.author_id')
            ->Published()->select('articles.*', 'authors.phone')->get();

[staudenmeir]

The qualified updated_at column was introduced in #26031. I'll look into it.

Please provide more details on the scope issue. What's the query and the error?

[driesvints]

Please fill out the issue template.

[Adam-78]

Original post updated using issue template with detailed information.

[staudenmeir]

The scope issue is unrelated to the change in Laravel 5.8. This has always been the "correct" behavior.

You can read all about it in laravel/ideas#347.