Laravel - Pivot table for three models - how to insert related models?

Question

I have three models with Many to Many relationships: User, Activity, Product. The tables look like id, name. And in the each model there are functions, for example, in User model:

public function activities()
{
    return $this->belongsToMany('Activity');
}
public function products()
{
    return $this->belongsToMany('Product');
}

The pivot table User_activity_product is:

id, user_id, activity_id, product_id. The goal is to get data like: User->activity->products. Is it possible to organize such relations in this way? And how to update this pivot table?

Carolinecarolingian

Answer 1

First I suggest you rename the pivot table to activity_product_user so it complies with Eloquent naming convention what makes the life easier (and my example will use that name).

You need to define the relations like this:

// User model
public function activities()
{
    return $this->belongsToMany('Activity', 'activity_product_user');
}
public function products()
{
    return $this->belongsToMany('Product', 'activity_product_user');
}

Then you can fetch related models:

$user->activities; // collection of Activity models
$user->activities->find($id); // Activity model fetched from the collection
$user->activities()->find($id); // Activity model fetched from the db

$user->activities->find($id)->products; // collection of Product models related to given Activity
// but not necessarily related in any way to the User

$user->activities->find($id)->products()->wherePivot('user_id', $user->id)->get();
// collection of Product models related to both Activity and User

You can simplify working with such relation by setting up custom Pivot model, helper relation for the last line etc.

For attaching the easiest way should be passing the 3rd key as a parameter like this:

$user->activities()->attach($activityIdOrModel, ['product_id' => $productId]);

So it requires some additional code to make it perfect, but it's feasible.

Duration

Just a Note, to update the values in Pivot table you can use sync method instead of attach method in Laravel 5, and updateExistingPivot in Laravel 4. – Jelena

Answer 2

The solution was found with some changes. In the models relationships look like:

// User model
public function activities()
{
    return $this->belongsToMany('Activity', 'user_activity_product', 'user_id', 'activity_id')->withPivot('product_id');
}
public function products()
{
    return $this->belongsToMany('Product', 'user_activity_product', 'user_id', 'product_id')->withPivot('activity_id');
}

To update pivot table:

$user->products()->save($product, array('activity_id' => $activity->id));

- where product and activity ids I get from Input. And, for example, to check if "user -> some activity -> some product is already exists":

if ($company->activities->find($activity_id)->products()->where('product_id', '=', $product_id)->wherePivot('company_id', $company_id)->get()->count() > 0) {
            // code...
}

I think it needs improvements but it works for me now.

Carolinecarolingian

There are several other threads in this forum asking about the exact same thing (three way pivot table). I implemented this solution in my application and it works great. This is the ideal approach for my situation. For this example, the query I would use to find all activities for a company for a specific product would look like this... $activities = $company->activities()->where('product_id', '=', 123) – Highbred

When you write: $user->products()->save($product, array('activity_id' => $activity->id)); I guess you really mean: $user->products()->save($product_id, array('activity_id' => $activity_id)); ? – French