Robert Stanley Robert Stanley - 2 months ago 16
MySQL Question

Laravel Eloquent and 3 table relationship

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

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