Framework: [Request] Change pivot table columns

[Sopamo]

I can't find anything on this in the documentation, neither in the code.

Imagine you have three tables: users, roles, user_role.
user_role has a column 'expires'.
Is it possible to change the expires column for existing relationships?

[cmenke]

Have a look at Working With Pivot Tables.

Notice that each Role model we retrieve is automatically assigned a pivot attribute. This attribute contains a model representing the intermediate table, and may be used as any other Eloquent model.

Hence, this slightly modified example should work just fine:

$user = User::find(1);

foreach ($user->roles as $role)
{
    $role->pivot->expires = '2013-12-21';
    $role->pivot->save();
}

[Sopamo]

Thanks for your answer! And how would I modify the pivot entry with, id $x? Iterating over all roles seems not to be the way to go.

[JoostK]

A pivot table which has an ID by itself? This isn't the case for L4 anymore, is it?

[jasonlewis]

@Sopamo If you know the ID for the pivot table why can you not just fetch it and change it? Why do you need to go through the relationship?

[cmenke]

As Jason suggested, you could simply do a Fluent Update:

DB::table('user_role')->where('id', $x)->update(array('expires' => '2013-12-21'));

[Sopamo]

@cmenke thanks, I missed the simplest way :/

[Sopamo]

Just one more for the record: How would I edit the pivot data from the user with id $x?

[cmenke]

1. You can iterate through related roles as shown in my first comment.
2. You can use Fluent to update the user_role Table directly:

DB::table('user_role')->where('user_id', $x)->update(array('expires' => '2013-12-21'));

Hope this helps! If you have more questions, #laravel on freenode is a great place to hang out :-)

[Sopamo]

Thanks @cmenke again. I talked with some guys on #laravel but they don't see a solution here:

What you wrote:

DB::table('user_role')->where('user_id', $x)->update(array('expires' => '2013-12-21'));

Would have to be:

DB::table('user_role')->where('user_id', $x)->where('project_id',$project->id)->update(array('expires' => '2013-12-21'));

Shouldn't there be an easier way to do this? For example:

$project->users()->find($x)->pivot->expires = '2013-02-02';

[cmenke]

If you know both IDs, your second code snippet would be correct. I'm afraid I'm not aware of any simpler Syntax in Eloquent / Fluent either.

If you need a simpler Syntax in your application, you can always write your own methods in your model.
Based on the User-Role example, you could do something like this:

// models/role.php
// I've taken Role as the starting point, you could implement this similarly in the User-Model
class Role extends Eloquent {

  ...

  // Instead of the User-Model you could also just pass the ID and use that instead of $user->id in the where clause
  public function updateUserPivot($user, $expires)
  {
    return DB::table('user_role')
      ->where('role_id', $this->id)
      ->where('user_id', $user->id)
      ->update(array('expires' => $expires));
  }
}

Usage:

// SomeController.php

...

$role = Role::find($x); // Retrieve the Role-Model
$user = User::find($y); // Retrieve the User Model (or - as mentioned above - just get the user ID if you don't need the User Object.. saves you one query :-)
$expires = '2013-02-02'; // Set an expiration date

$success = $role->updateUserPivot($user, $expires);

if ($success == 1)
{
  // Fluent's DB::update should return the number of affected rows.
  // Since we provided both foreign IDs, $success should be == 1
  echo "success!!";
}

There are many ways to tweak this example to suit your needs, but you should get the idea.

[Sopamo]

Thanks @cmenke these are some great how to's for pivot tables collected here. Maybe more information on this topic should also be in the documentation.