Using parameters in DB::raw(...) in Eloquent update(...) method is poorly supported.

[weaversam8]

It's often more performant to rely on your database's storage engine to do bulk updates to database records rather than looping thru the Eloquent objects and updating them in PHP. Fortunately, Laravel provides the update(...) method on both the Eloquent and the regular query builder to assist with this.

It's a common desire to reference other database columns rather than updating records with a static value. For example, you may want to use a builtin database function (like one of PostgreSQL's JSON functions) in the query to update a column. The only way to do this in any sort of supported way in Laravel is to use the DB::raw(...) method. Unfortunately, usage of this method within the update(...) method is not well documented or supported.

Where other portions of the query (i.e. SELECT, WHERE, HAVING, etc...) have dedicated support methods for using the DB::raw(...) method with them (selectRaw(...), whereRaw(...), havingRaw(...), etc...), no such method exists for the UPDATE portion of the query (or perhaps more accurately, the SET portion of an update query.)

Because of this, it's also not possible to use PDO parameters with the Eloquent query builder. If someone attempts to use them, such as in this somewhat hacky example:

$eloquent_qb->addBinding('value');
$eloquent_qb->update(['column' => DB::raw('? || "column"')]);

The manually added binding won't function appropriately, due to the binding array being shifted by the Eloquent query builder's call to addUpdatedAtColumn(...). As of right now, the only way to do this is by dropping to the base query builder, using toBase(), as in this modified example:

$eloquent_qb->addBinding('value');
$eloquent_qb->toBase()->update(['column' => DB::raw('? || "column"')]);

It would be great to have first class support for raw update queries!

[flexchar]

Came here looking for exactly this thing after watching https://eloquent-course.reinink.ca/ eloquent performance course. This should absolutely be documented as well as encouraged. Not only it saves time/yields better performance but it benefits absolutely everyone from developer to the end users and to keeping our planet greener! Would love to see official support!

@weaversam8 thanks for sharing this! I'm struggling to get the example working, and also to understand how exactly this works and why you're adding a binding. I'd be very glad if someone could explain me this.

EDIT: I figure out! This took 10ms updating 100rows on a dev base. These are insane speed savings considering how much more it'd take time updating big database using ->each() method.

Right now I'm trying to achieve something like this:

$query = Conversation::toBase()
    ->rightJoin('teams', 'teams.user_id', '=', 'conversations.user_id')
    ->addSelect([
        'conversations.id',
        'conversations.user_id',
        'teams.id AS teams_id',
        'teams.user_id as teams_user_id',
    ]);        

$count = $query->update([
    'conversations.team_id' => DB::raw('`teams.id`'), 
    // Notice how I forgot to wrap each word with ticks and instead I wrapped full path with ticks here. 
    // Should be DB::raw('`teams`.`id`') and it works!
]);

(every user has a team, and I've added a new teams_id on conversations table which I wanna fill out using database query; I could do it the ordinary way but I'm here for the challenge that will teach me database performance tricks)