Update query throws "SQLSTATE[HY093]: Invalid parameter number" error. Parameters don't get passed on

Ok, this thing is so weird I have zero explanation for it, but here's the solution:

1. First of all you need to explicitly update updated_at yourself, because otherwise Laravel would try and do it itself and make a big mess out of everything.

2. For some unknown reason the initial setup simply failed to assign the variables to the placeholder ?: DB::raw("REPLACE(hierarchy, ?, ?)", [$old_hierarchy, $new_hierarchy])

3. I had to do it using setBindings, but on top of that couldn't assign all of them through this method because they would have gotten messed up once again, so only did it for the DB::raw statements.

Category::setBindings([$old_hierarchy, $new_hierarchy, now()])
    ->whereRaw("hierarchy LIKE ?", [$old_hierarchy . '>%'])
    ->update([
        'hierarchy' => DB::raw("REPLACE(hierarchy, ?, ?)"),
        'updated_at' => DB::raw("?")
    ]);

If anyone has any explanation of this whole crazy thing, please let me know

I'm not sure why you are using raw sql. Why cant you just do this?

Category::where('hierarchy', 'like', $old_hierarchy.'>%')  //is > supposed to be there?
		->update([
		'hierarchy' => $new_hierarchy
	]);

Also, when you do it this way, updated_at will update properly.

It's because I'm only replacing a part of the field and not the whole field. Think of it in terms of applying str_replace on a string instead of changing it entirely.

And for that I need to use MySQL's REPLACE function.

For example, in a A>B>C>D hierarchy, I need to replace A>B>C with X>Y, so the result would be X>Y>D

I have noticed Laravel has several limitations when trying to use more complex queries, so you have to stay within those limits.

So why not just keep it simple:

Category::where('hierarchy', 'like', $old_hierarchy.'>%')
		->update([
		'hierarchy' => DB::raw("REPLACE(hierarchy, '$old_hierarchy', '$new_hierarchy')"
	]);

Or go full raw if thats your preference:

DB::raw("
	UPDATE categories 
	SET 
    		hierarchy = REPLACE(hierarchy,
        		'$old_hierarchy',
        		'$new_hierarchy'),
		updated_at = NOW()
	WHERE
    	hierarchy LIKE '$old_hierarchy>%';
");

Either one is more readable than ? and bindings.

Because I figured these may be subject to SQL injection.

Whenever you are using raw, you must make sure you validate the data properly and you should be good.