Laravel ORM Friendship Relationship without duplication
laravel, laravel-4, orm, php
Solution
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.
Problem
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).