Laravel ORM Friendship without duplication

Question

What is the best way to model friendships with an eloquent one? My table layout is below, and I would like to define one relationship in which I could get all the friends, for example, 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 ratio above is close to work when I'm looking for friends with user id 3 I get users 1 and 3, but obviously I want 1 and 2.

Friendship table

user_id: ID of the user who requested friendship
friend_id: Target friend user id
state whether the friendship is waiting, accepted or blocked.
created_at and updated_at

I know there are solutions from Laravel. Many of the many links to the lookup table work in only one direction , where I can find friends on both sides of the relationship, but I have to have two lines, for example, if users 1 and 3 are friends, then on the same line user_id = 3 and friend_id = 1, and in the next line, vice versa. (Or, if I don't have two lines, I have to execute two queries).

Answer 1

You should not try to turn what should be two lines into one line, but if you try to do this, you do not have to hit the database twice:

 select * from users where (user_id = :user_id and friend_id = :friend_id) or (friend_id = :friend_id and user_id = :user_id) 

In Laravel there will be:

 Users::whereRaw('(user_id = ? and friend_id = ?) or (friend_id = ? and user_id = ?)', [ $user_id, $friend_id, $friend_id, $user_id ]); 

You can also do subgroups to group them, but it's a little complicated.

Answer 2

You can do two searches, and use the join query , so only hit the database once. Put it all 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); } } 

Answer 3

I have an assumption that you are using conditions to load the values ​​you want

• in this example, suppose you used a request to load user_id and fiend_id with the condition

  "select * from frienddship WHERE user_ID = '$ id' OR friend_ID = '$ id'"

$ id: The identifier of the user you want to show to your friends.

inside a WHILE loop in PHP that will be used to load results that you can filter by fulfilling the condition

 while ... { if (friendship['user_id'] == $id) { $f_id = friendship['friend_id'] ; } // other instructionS... else { $f_id = friendship['user_id'] ; } // other instructionS... } 

in this case, you will load data from the columns of the bothe table, and then each time filter the column with the user ID and only allow its friend ID. The filter did not tell the user that you are friends with yourself.

sorry i used mysql to explain an example