Laravel Many to many self referencing table only works one way

Question

I have set up the relationship and the models as below:

pivot table schema

Schema::create('friend_user', function(Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned();
    $table->integer('friend_id')->unsigned();
    $table->timestamps();
});

pivot table seeder (this defines two 'friendships' to which user '1' belongs one where user 1 is specified in the user_id and the second where user 1 is listed in the friend id):

    $friend_user = array(
        array(
            'id' => 1,
            'user_id' => 1,
            'friend_id' => 3,
        ),
        array(
            'id' => 2,
            'user_id' => 4,
            'friend_id' => 1,
        ),

    );

User model

public function friends()
{
 return $this->belongsToMany('User', 'friend_user', 'user_id', 'friend_id');
}

This is as suggested by Taylor Otwell here: https://github.com/laravel/framework/issues/441

This all works but when I run the following command I get only one result:

foreach(Auth::user()->friends as $i) {
    var_dump($i->id);
}

This returns the value '3' but not 4 as expected. I understand why this is happening (as the user_id is not the friend_id) but how can I get this to return a collection of all friends belonging to a user (ie all friendships) regardless of which end of the connection (user_id or friend_id) the user is?

you onlu have 2 relations in your seeder, one for two different users. So your function is only going to return one result for each of those users? — Laurence, Jul 10 '13 at 10:53
Yes, but the friend_id on the second record is the id of user 1 so both entries define a friendship, it just happens that one was initiated by one user and the other by the second user. I'm trying to work out how to return friendships regardless of the order they are entered into the system. — Al_, Jul 10 '13 at 10:55
oh - i get it now... perhaps insert two records when you make a friend? one for 'each way' of the friendship? that would be easiest. — Laurence, Jul 10 '13 at 11:52
That's my backup plan, I was hoping there was a cleaner way of doing it though — Al_, Jul 10 '13 at 12:03
In a bit of a cop out I changed the how the site works so that people follow others rather than friend them (not just to solve this issue mind!) so I only needed one way. Having had more experience now I would probably try and solve this by using raw queries and if statements within the sql, something like (off top of my head so prob errors): `SELECT IF (user_id = , friend_id, user_id) as id_of_friend WHERE user_id = OR friend_id = ;` where id is the id of the user whose friends you want to retrieve. Once you had that working use it as a subquery to pull out the users with ids IN — Al_, Feb 20 '14 at 15:13
For people stumbling on this, have a look here: http://stackoverflow.com/questions/5612736/how-to-implement-a-friendship-model-in-rails-3-for-a-social-networking-applicati — tomvo, Jul 17 '14 at 07:06
If anyone landed here due to google, the discussion regarding this issue was recently restarted in a newer thread: https://stackoverflow.com/questions/55113114/laravel-eloquent-inner-join-on-self-referencing-table I proposed a solution [here](https://stackoverflow.com/a/55752491/11335307), any further ideas and contributions to this issue welcome. — tomstig, Apr 21 '19 at 23:34

score 22 · Answer 1 · answered Jul 21 '13 at 01:53

22

Instead of creating two records use a new function.

public function friends()
{
  return $this->belongsToMany('User', 'friend_user', 'user_id', 'friend_id');
}

// Same table, self referencing, but change the key order
public function theFriends()
{
  return $this->belongsToMany('User', 'friend_user', 'friend_id', 'user_id');
}

//You can then call opposite record(s) using:
foreach( Auth::user()->theFriends as $theFriends )

I used this approach in my project so I can have better separation for organizing the results.

answered Jul 21 '13 at 01:53

gettingThere

237
1
2

2

This would require calling both friends() and theFriends() separately to get the collection of all friends. Hardly a very good solution, especially once you factor in the double SQL queries. – Mike Feb 20 '14 at 11:25
5

Great example for a self referencing many-to-many relationship, maybe the names are implemented a little weird, but the basic principle holds up. +1 sir. – Dylan Pierce Aug 21 '15 at 17:01
yeah good example although the names are terrible. confusing. – AndrewMcLagan Mar 02 '18 at 15:01

score -2 · Answer 2 · answered Jul 13 '15 at 11:43

-2

use this

public function friends()
{
 return $this->belongsToMany('User', 'friend_user', 'user_id', 'friend_id')->orWhere('friend_id', $this->id);;
}

in this case you get one query and one record for each relation

answered Jul 13 '15 at 11:43

Ando Hak

1
1

When you try to get the friends on the other side, you always return yourself because it uses the friend_id – Daniel Luca CleanUnicorn Nov 04 '15 at 15:06

Laravel Many to many self referencing table only works one way

2 Answers2

Linked