updated_at column ambigious while updating with eloquent

Question

I have 3 table cart,pharmacy and cart_pharmacies

cart table

cart_id | user_id | total_price | status | created_at | updated_at

pharmacy table

pharmacy_id | name | address_id | created_at | updated_at

cart_pharmacies table

cart_pharmacies_id | cart_id | pharmacy_id | created_at | updated_at

In cart modal i define relation

   public function pharmacy()
    {
        return $this->belongsToMany('App\Pharmacy','cart_pharmacies','cart_id','pharmacy_id');
    }

In parmacy modal i define

public function cart()
{
    return $this->belongsToMany('App\Cart','cart_pharmacies','pharmacy_id','cart_id');
}

In controller i have pharmacy_id i am trying to update cart status with code

$pharmacy_id=$request->input('pharmacy_id');
$pharmacy=  Pharmacy::findOrFail($pharmacy_id);
$pharmacy->cart()->update(['status'=>1]);

but it is giving me error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 
'updated_at' in field list is ambiguous (SQL: update `cart` inner join 
`cart_pharmacies` on `cart`.`cart_id` = `cart_pharmacies`.`cart_id` set 
`status` = 1, 
`updated_at` = 2016-05-31 07:14:47 where `cart_pharmacies`.`pharmacy_id` = 5)

That is the problem you are missing the single quotes around the updated_at value. There seems to be some common code which is passing the value. Have a look at it. — chandresh_cool, May 31 '16 at 07:24
Not this is not a problem the problem is botth table cart_pharmacies and cart have updated_at column it cause ambiguous. i don't know how to solve that problem — Umar Akbar, May 31 '16 at 07:29
If you feel this is not the issue, just try running the output sql you added i.e. (update `cart` inner join `cart_pharmacies` on `cart`.`cart_id` = `cart_pharmacies`.`cart_id` set `status` = 1, `updated_at` = 2016-05-31 07:14:47 where `cart_pharmacies`.`pharmacy_id` = 5 ) in your phpmyadmin or any sql ui, you would get same error. — chandresh_cool, May 31 '16 at 07:30
Actually the quote thing is handle by default in laravel. We have nothing to do with that. And even if i consider that its the problem with the quotes then the error will be different than this one. It will be a syntax error not about the ambigious. I am more concerned about how to handle the alias in the elequoent than handling the quotes as this can be done by laravel because thats the framework thing. — Umar Akbar, May 31 '16 at 07:44
I know it's an old question, but maybe duplicated of [this](https://stackoverflow.com/questions/25320335/eloquent-update-failing-due-to-updated-at-table-ambiguity/25325707), and there is an useful answer to this question: https://stackoverflow.com/a/25325707/2116875 — rneves, Jul 06 '17 at 19:00
@chandresh_cool Did you understand that those queries are composed by Eloquent? — Jhourlad Estrella, Aug 31 '18 at 03:18

score 2 · Answer 1 · answered May 31 '16 at 07:56

2

SQL query error says there is multiple columns named 'updated_at' (both main and related tables) and cant decide which to update. It is a bug and I think there is no way to solve with eloquent as updated_at will be added at the end automatically without the table name specified.

answered May 31 '16 at 07:56

muratyuksel

21
2

1

I am inclined to agree – Jonathan Mar 06 '17 at 10:04

score 2 · Answer 2 · answered Mar 12 '20 at 14:22

2

$pharmacy->cart()->toBase()->update(['status'=>1, 'cart.updated_at' => now()]);

Use toBase() method and add 'cart.updated_at' => now() to update data

answered Mar 12 '20 at 14:22

Josh Alecyan

1,136
8
19

Thank you for your answer. Hope it helps others. – D. O. Mar 12 '20 at 14:51
Wow !! Works like a charm. In my case the update query had an inner join and both had `updated_at` . – Daniyal Nasir Jan 03 '22 at 15:59

score 0 · Answer 3 · answered May 31 '16 at 15:34

Your are using timestamps in your pivot table which could be automatically controlled by Eloquent with withTimestamps() method.

In your parmacy modal:

public function cart() {

return $this->belongsToMany('App\Cart','cart_pharmacies','pharmacy_id','cart_id')->withTimestamps();

}

score 0 · Answer 4 · answered Aug 19 '18 at 21:39

The only solution I found so far is the following:

Given that you have an array of values to update ($values) and your query ($query), you simply create a base query and add your columns manually:

$now = now();

// Manually add the timestamp columns
$values = $values + [
    'table.' . Model::CREATED_AT => $now,
    'table.' . Model::UPDATED_AT => $now,
];

$query->toBase()->update($values);

Model is an import of Illuminate\Database\Eloquent\Model, table is the name of the table you really want to update.

Not the best but it works, maybe this will become a feature in the future…

score 0 · Answer 5 · answered Feb 11 '19 at 20:34

0

Here is Link to overcome the issue

https://github.com/laravel/framework/issues/13909

The use of toBase() before updating assist me so much to solve the issue happy coding.

answered Feb 11 '19 at 20:34

Ruberandinda Patience

3,435
3
20
18

Chamandeep · Answer 6 · 2016-05-31T08:21:58.923

-1

If you use modal please update your modal with this line

public $timestamps = false;

try, it may be helpful

edited May 31 '16 at 08:21

answered May 31 '16 at 08:04

Chamandeep

116
2
8

i have done this it works but it does not store updated_at value – Umar Akbar May 31 '16 at 08:06
1

set your TIMESTAMP default value by CURRENT_TIMESTAMP in your database – Chamandeep May 31 '16 at 08:49

updated_at column ambigious while updating with eloquent

6 Answers6