Laravel eloquent update column using relationship column

Question

How can achieve this query?

Sale::with(['catalog'])
    ->whereIn('id', $ids)
    ->update(['price' => DB::raw('catalog.price')]);

This is not working, it shows undefined table... I tried to put the name of the table but it's the same.

On the internet I always found the easy query:

Sale::with(['catalog'])
    ->whereIn('id', $ids)
    ->update(['price' => 5]);

Okay! When I want to update all rows with the same value is easy, in addition is easy when you want to update with a column of the same table like:

Sale::with(['catalog'])
    ->whereIn('id', $ids)
    ->update(['price' => DB::raw('price_alternative')]);

But how about using a column of another table with a relationship? I haven't found the solution.

I know this can be solved using entire raw query, but I wanted to know if it can be achieved by the eloquent way

score 5 · Accepted Answer · answered Jul 31 '20 at 07:26

5

You probably need to join in the catalog table on your querybuilder. This is different than using with(). Something along the lines of

Sale::whereIn('id', $ids)
    ->join('catalog', 'sales.catalog_id', '=', 'catalog.id')
    ->update(['price' => DB::raw('catalog.price')]);

answered Jul 31 '20 at 07:26

Qirel

Yep I thought about this. I always try to use relationships instead of joins but if there is no alternative I'll go with this. Thank you. – Marc Pont Jul 31 '20 at 07:29
Indeed, its always better to use relations where applicable, but this is one of those cases where you'd need a join. Cheers! – Qirel Jul 31 '20 at 07:53

score 0 · Answer 2 · answered Jul 31 '20 at 09:06

0

This is not better than answer of @Qirel, but it is Eloquent way, i like this because that's more clearly.

$Sales = Sale::whereIn('sales.id', $ids)
    ->with('Cat')->get();

$Sales->map(function($q){
    $q->price = $q->Cat->price;
    $q->save();
});

Assume you have this relation code in Sale model:

public function Cat()
{
    return $this->hasOne(CatModel::class, 'id', 'catalog_id');
}

answered Jul 31 '20 at 09:06

3

This can have a massive performance impact depending of the $ids length. – Marc Pont Jul 31 '20 at 09:37
Yes i know, but try a "pure" Eloquent way. You can use Chunk and Transaction to control performance. That's why i said "this is not better than answer of @Qirel" – Trần Hữu Hiền Jul 31 '20 at 12:49

2 Answers2