Friendship system with Laravel : Many to Many relationship

Question

I'm trying to create a Friendship system with Laravel (I'm starting with it) but I'm blocked with relationships. Here's the thing : there is one table Users and one table Friends which contains the following columns :

friends: id, user_id, friend_id, accepted. 

It looks like a Many to Many so here's what I set on User class :

class User extends Eloquent {     function friends()     {         return $this->belongsToMany('User');     } } 

But when I try a :

$friends = User::find($id)->friends()->get() 

I have this error :

Base table or view not found: 1146 Table 'base.user_user' doesn't exist 

I would like to get a list of the Friends of a user, no matters if the user sent the invitation or received it. So the user can ba on user_id or on friend_id and then I retrieve the data of the other user depending of that column.

Any idea? Thank's!

EDIT : Here's the code I use :

$usersWithFriends = User::with('friendsOfMine', 'friendOf')->get(); $user = User::find(Auth::id())->friends;  foreach($user as $item) {     echo $item->first()->pivot->accepted; }  

Answer 1

tldr; you need 2 inverted relationships to make it work, check SETUP and USAGE below

---

First off the error - this is how your relation should look like:

function friends() {   return $this->belongsToMany('User', 'friends', 'user_id', 'friend_id')     // if you want to rely on accepted field, then add this:     ->wherePivot('accepted', '=', 1); } 

Then it will work without errors:

$user->friends; // collection of User models, returns the same as: $user->friends()->get(); 

---

SETUP

However you would like the relation to work in both ways. Eloquent doesn't provide a relation of that kind, so you can instead use 2 inverted relationships and merge the results:

// friendship that I started function friendsOfMine() {   return $this->belongsToMany('User', 'friends', 'user_id', 'friend_id')      ->wherePivot('accepted', '=', 1) // to filter only accepted      ->withPivot('accepted'); // or to fetch accepted value }  // friendship that I was invited to  function friendOf() {   return $this->belongsToMany('User', 'friends', 'friend_id', 'user_id')      ->wherePivot('accepted', '=', 1)      ->withPivot('accepted'); }  // accessor allowing you call $user->friends public function getFriendsAttribute() {     if ( ! array_key_exists('friends', $this->relations)) $this->loadFriends();      return $this->getRelation('friends'); }  protected function loadFriends() {     if ( ! array_key_exists('friends', $this->relations))     {         $friends = $this->mergeFriends();          $this->setRelation('friends', $friends);     } }  protected function mergeFriends() {     return $this->friendsOfMine->merge($this->friendOf); } 

USAGE

With such setup you can do this:

// access all friends $user->friends; // collection of unique User model instances  // access friends a user invited $user->friendsOfMine; // collection  // access friends that a user was invited by $user->friendOf; // collection  // and eager load all friends with 2 queries $usersWithFriends = User::with('friendsOfMine', 'friendOf')->get();  // then $users->first()->friends; // collection  // Check the accepted value: $user->friends->first()->pivot->accepted; 

Answer 2

It's oviously a problem in your DB and also definition of the relation. Many-to-Many relation type expects you to use and intermediate table. Here's what you have to do :

1. Create a user_friend (id, user_id, friend_id) table in your schema.
2. Remove unnecessary fields from user and friend tables.
3. Create proper foreign keys . user.id-> user_friend.user_id , friend.id -> user_friend.friend_id
4. Better define full relation on the User and Friend models,

for example :

 class User extends Eloquent {     function friends()     {         return $this->belongsToMany('User', 'user_friend', 'user_id', 'friend_id');     } } 

You can read much more in Laravel docs, HERE