Laravel Eloquent and 3 table relationship

Question

I'm looking for some help in working out how to setup the Eloquent relationships for my application.

I have created migrations for three tables.

| users     |   | items     |   | user_items |
+-----------+   +-----------+   +------------+
| id        |   | id        |   | id         |
| username  |   | name      |   | user_id    |
                                | item_id    | 
                                | qty        | 

I have setup an unique index on the user_items table, limiting 1 user id to 1 item id, but with a qty column. I want to setup Eloquent so that I have two models, User and Item. Where I could say:

$item = $user->items()->first();
$name = $item->name;
$qty  = $item->qty;

The problem is I'm trying to figure out if I need a 3rd model, UserItem or not.

Answer 1

What are you doing here is actually a M:M relationships, and Laravel Eloquent already have support for that out of the box. The user_item table you have is referred to as the pivot table.

Here's how to setup your relationship using Eloquent.

// User class
class User extends Eloquent {

 public items() {
   return $this->belongsToMany(Item::class)->withPivot('qty');
 }

}

class Item extends Eloquent {
  public function users() {
       return $this->belongsToMany(User::class)->withPivot('qty');
  }
}

For this to work, you will need three migrations:

• one for the user table
• one for the item table
• one for the item_user table. In Eloquent, the pivot table consists of the two tables names in alphabetical order. separated with a dash.
• following Eloquent conventions, if you set the FK to use _id, Eloquent will not need additional information. So, in your case, the FK in the item_user table should be user_id and item_id respectively.
• There is no need for a ItemUser model. You can access the pivot in the relationship by doing:

$user = User::find(1);
$user->items[0]->pivot->qty; // retrieve the quantity