Laravel ORM Friendship Relationship without duplication

Question

What is the best way to model a friendship relationship with eloquent? My table schema is below and I would like to define one relationship where I could retrieve all the friends something like as follows.

<?php

class User extends Eloquent {

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

+----+---------+-----------+----------+---------------------+---------------------+
| id | user_id | friend_id | state    | created_at          | updated_at          |
+----+---------+-----------+----------+---------------------+---------------------+
|  1 |       3 |         1 | accepted | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  2 |       2 |         3 | accepted | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+---------+-----------+----------+---------------------+---------------------+

The relationship above is close to working when I looks for friends with the user id of 3 I get users 1 and 3 but obviously I want 1 and 2.

Friendship Table

user_id: The User Id who requested the friendship
friend_id: The User Id of the targeted friend
state: Whether the friendship is pending, accepted or blocked.
created_at and updated_at

I am aware that there are solutions from Laravel Many to many self referencing table only works one way where I can retrieve the friends from both sides of the relationship but I must be two rows, for example if user 1 and 3 are friends, then in one row user_id = 3 and friend_id = 1, and in the next row vice versa. (Or if I don't have two rows I must do two queries).

Answer 1

You could do two lookups, and use a union query, thus only hitting the database once. Put all of this in a custom function:

class User extends Eloquent {

  public function friends()
  {
     $first = $this->belongsToMany('User', 'friendships', 'user_id', 'friend_id');  
     return $this->belongsToMany('User', 'friendships', 'friend_id', 'user_id')->union($first);  
  }
}