Sync many to many based on a given column

Hi,

I'm looking for some help on how to solve the syncing on many to many relationships 'the right way'.

So I have 3 tables, a Users table, a Categories table and a Prices table which is also a pivot. What I'm looking for is a way to sync the Prices table based on the list of categories (it's an array of IDs) received from a form.

Users and Categories as basic tables, here is the Prices table:

Schema::create('price', function (Blueprint $table) {
    $table->increments('id');
    $table->uuid('user_id');
    $table->integer('category_id');
    $table->double('price')->default(0);
    $table->string('currency')->default('USD');
    $table->timestamps();
});

And here are the models:

User.php

public function prices()
{
    return $this->hasMany(Price::class);
}

public function categories()
{
    return $this->hasManyThrough(Category::class, Price::class, 'user_id', 'id', 'id', 'category_id');
}

Category.php

public function users()
{
    return $this->hasMany(User::class);
}

public function price()
{
    return $this->hasOne(Price::class);
}

Price.php

class Price extends Pivot
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function category()
    {
        return $this->hasOne(Category::class);
    }

    public function categories()
    {
        return $this->hasMany(Category::class);
    }
}

What I'd like to do is:

$user->prices()->sync('category_id' => $request->categories);

Is there a way of doing this in one go (i.e. 'magically' like the sync() method) in Laravel or do I have to resort to filters and whatnot to accomplish such a feet? And if so how do I take care of all 3 conditions?

Condition 1: if there is a price for that category in the list of categories for this user, do nothing. Condition 2: if there isn't a price for that category in the list of categories for this user, create one. Condition 3: there are other categories not found in the list for which prices exist for the user, remove them

The only way I could figure out how to do this is to remove all the prices for the user and recreate new ones based on the list of categories.

Is that a good way to do it? Personally, I think it isn't and I'm wasting resources doing it this way.

P.S. I know I'm probably spoiled by how Laravel is designed and am probably looking for some 'magical' ways of doing things but at the same time I feel I'm not doing it in the most efficient way.

Thanks, J

lostdreamer_nl

7 years ago

Best Answer

Level 53

That should be possible yes, I've never used the hasManyThrough method for this though, so I would probably do it like this:

User.php:

public function categories()
{
    return $this->belongsToMany(Category::class, 'prices')
        ->using(Price::class)
        ->withPivot('price', 'currency');
}

This will give you the possibility of using sync() like:

$user = User::find(1);
$user->categories()->sync( [
    3 => [ 'price' => 300, 'currency' => 'EUR' ],
    6 => [ 'price' => 500, 'currency' => 'USD' ],
    8 => [ 'price' => 700, 'currency' => 'EUR' ]
]);

Pricing would be found like:

foreach($user->categories as $category) {
    // $category->pivot  will be your Price class
    dd($category->pivot->price, $category->pivot->currency);
}

Untested, but I think it should work, and you can still use your Price pivot model as a regular model as well...

1 like

j3rik0

7 years ago

Level 6

@lostdreamer_nl that works great, just the way I was looking for. I tend to stay away from hasManyThrough too, this is like the second time I use it, just because I found it to be appropriate, at least visually pleasing to not use the pivot in the chain.

Please or to participate in this conversation.