Framework: updated_at not fully qualified, creates ambiguous column errors

[bryceray1121]

When attempting to use delete() or update() on an eloquent model you can not use any joins because you will get an ambiguous column name error on updated_at. This is because the Eloquent/Builder.php does not prefix updated_at with the proper table name. I attempted to do a pull request for this, but the issue is further complicated by Arr::add (used in addUpdatedAtColumn) doing automatic dot notation exploding. This prevents me from adding a table.columnName string similar to the operation of SoftDeletes trait.

Laravel 5.2

[srmklive]

You can disable $timestamps on specific models by doing this: public $timestamps=false.

One question though, why are you using join when updating or deleting a row? Can you describe in detail the exact scenario?

[themsaid]

There was an attempt to fix a similar issue: https://github.com/laravel/framework/pull/13519
But end up causing problems with Postgres and also sqlite: https://github.com/laravel/framework/issues/13707

So the fix was reverted: https://github.com/laravel/framework/commit/d12c4bbf8b09ff9cf916a8e54b15e43752f31350

I don't think there'll be an easy solution for this as using a fully qualified column name table.column has different rules between drivers which requires having different implementations.

[troygilbert]

This seems like a pretty major issue. Maybe I'm using Eloquent wrong, but with just a simple use of Eloquent like this: $model->childRelationship()->update(['some_column' => $value]); fails.

[samrap]

Yeah it is definitely an issue. The commit referenced by @themsaid was mine, and while I haven't had the time to revisit this issue I am going to devote the time to look for a fix regardless. Anyone who wants to work together to find a solution, you can find contact info on my profile. Really determined to get this resolved.

[samrap]

The only thing that comes to the top of my head is in the Eloquent Model's getQualifiedUpdatedAtColumn method, checking which driver is being used and determine the value that way.

The more long-term way would probably be defining the Eloquent Builder as an abstract class and then each driver has its own implementation of the addUpdatedAtColumn method. Of course, that would require Laravel to determine the proper Builder at runtime.

[themsaid]

@samrap Did you get the chance to open a PR?

[samrap]

@themsaid Unfortunately not but I'd be happy to work together with you in order to develop a solution. From what I've discovered with this bug it's a much larger issue than it sounds.

[samrap]

@themsaid I don't have experience outside of MySQL but if I get a list of the different qualified column name rules between drivers I think I can handle fixing this issue on my own.

[baoanhng]

This seems to be an edge case and there hasn't been a fix yet.
But we can always attempt to solve with the Query Builder. Join the tables and manually set deleted_at and updated_at.

[themsaid]

@trandaihung that's what I personally do, I tried to fix before but every database driver has different rules for the qualified column name which makes this change pretty major.

[makoru-hikage]

Aw, I've to use Carbon::now()

[iateadonut]

Can a method ->withoutTimestamps() be added for this particular case?

[JordanDalton]

I've ran into this issue today as well.

[walterdeboer]

@srmklive the join in my case is generated because I have a belongsToMany relation in my model. When soft-deleting the model, the relation also gets soft-deleted.

@samrap I noticed however the deleted_at column is properly qualified in the delete query, where the updated_at column isn't. Is this the also the case for other scenario's? Why isn't updated_at qualified the same way as the deleted_at column?

[samrap]

@walterdeboer My PR to fix the issue explains what is going on in-depth: https://github.com/laravel/framework/pull/13519

Unfortunately, as @themsaid mentioned above, the PR caused a problem when using the Postgres and Sqlite drivers.

[decadence]

Have this issue as well

[jtwiegand]

I'm able to sidestep the ambiguity of the updated_at columns in Laravel 5.3 with a toBase() call on the Eloquent builder before doing an update.

[Turaylon]

Same problem here. As a workaround had to pluck the ids for the result that i want to update with a select and then update the model with the given ids.

    $ids = User::filter($filter)->pluck('id'); //in filter i have  a join with an other model that has timestamps
    User::whereIn('id',$ids)->update(['field' => 'value']);

[pr4xx]

Same issue here

[iget-esoares]

This issue is affecting be on L5.4.36, and also should affect L5.5

$query->join(/* join some table with updated_at */)
->delete()

This will bring ambiguous column error for updated_at

[samrap]

We're all well aware this issue exists. Can we refrain from unconstructive comments of the type "same thing happening here" from this point forward? There are lots of people subscribed to these issue threads that don't need to be getting emails every time someone reconfirms this is a bug 😬

[f4usto]

I am using the L5.4.36 and the workaround provided from @Turaylon is not feasible as it could get on the millions of ids on the current project and also the update values come from other tables that will be deprecated

I ended up using a ReflectionClass to "disable" the timestamps for the model on the source model for the query builder:

$instance = new \App\Models\Model;
$reflection = new ReflectionClass(\App\Models\Model::class);
$reflection->getProperty('timestamps')->setValue($instance, null);

$instance->/* wheres(), joins() and so on */->update([/* update values */])

This is not a long term approach or solution, but it bypass the timestamp modification. by not updating that field at all.

A driver aware implementation (or an alternative to the array splitting) of the addUpdatedAtColumn would be appreciated.

[Polokij]

I've used method toBase() before update
$someMode->relations()->someScope()->toBase()->update(['relationTable.deleted_at'=> now(), 'relationTable.updated_at' => now()]);
And it works for me.

[slaughter550]

@samrap I'm happy to help you solve this problem if you're still looking to solve it. I would also be hesitant about the function getUpdatedAtColumn knowing about the driver as that seems like an implementation detail that each driver should know how to FQ a property. Something that might be of note in this case is how each driver references properties on a join. I could see putting something like the addUpdatedAtColumn method a few levels deeper either in the Query Builder or at the Grammar level even. I would have to dig more but I would be keen on solving this issue with you if you're interested?

[samrap]

@slaughter550 I would definitely be interested in working together on a solution. I'll shoot you an email.

[tnatanael]

Its still happening, with Lumen 5.5 any news?

[staudenmeir]

22366 updated the SQLiteGrammar to remove the table name from qualified columns when an UPDATE query is compiled.

If we apply the same behavior to the PostgresGrammar, couldn't we resubmit @samrap 's PR (#13519)?

[amenk]

Sound's good to me, @staudenmeir can you make such a PR ?

[staudenmeir]

Will be fixed in Laravel 5.8: #26031

[laurencei]

Thanks @staudenmeir

[patieru12]

I'm able to sidestep the ambiguity of the updated_at columns in Laravel 5.3 with a toBase() call on the Eloquent builder before doing an update.

This works very nice for me. I am using Laravel 5.7

[robsonvn]

Keep in mind that toBase() only works because it does not update the updated_at date