CODEWITHSUNDEEP
phpormlaravellaravel-4
Thomas Clarkson
Thomas Clarkson

Reputation: 755

Laravel ORM Friendship Relationship without duplication

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).

Upvotes: 8

Views: 2656

Answers (3)

Phil Sturgeon
Phil Sturgeon

Reputation: 30766

You shouldn't try and turn what should be two rows into one row, but if you are going to try and do that then you definitely don't need 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)

That in Laravel would be:

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

You could also do sub-wheres to group them, but thats a little complicated.

Upvotes: 4

Hamza Trabelsi
Hamza Trabelsi

Reputation: 1

i have a suggestion that you use conditions to load the vlaues you want

  • in this example let's say you used query to load user_id and fiend_id with condition ,

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

$id : is the user id that you want to show his friends.

inside the WHILE loop in PHP that u will use to load the results you can filter resulets by making 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 bothe table columns and then each time filter the column with the user id and only let his friend's id , the filter used to not say to the user that you are friend with yourself.

sorry i used mysql to explain the example

Upvotes: -3

Laurence
Laurence

Reputation: 60058

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);  
  }
}

Upvotes: 6

Related Questions