So basically what I' trying to do is grab all the friends to a given user, but not grab the given user itself, only the friends. But I got stuck when I needed to have different joins, based on where's. Example:
If the given user is equal to user_id_sender, I would like join tables on user_id_receiver. And the other way around.
I came up with this:
DB::table('friends') -> where('friends.user_id_sender', $user_id)
-> orWhere('friends.user_id_receiver', $user_id)
-> join('profiles as profile_receiver', 'profile_receiver.user_id', '=', 'friends.user_id_sender')
-> join('profiles as profile_sender', 'profile_sender.user_id', '=', 'friends.user_id_receiver')
-> join('users as receiver', 'receiver.id', '=', 'friends.user_id_sender')
-> join('users as sender', 'sender.id', '=', 'friends.user_id_receiver')
-> select('receiver.name', 'receiver.id', 'profile_receiver.picture', 'sender.name', 'sender.id', 'profile_sender.picture', 'sender.last_seen', 'receiver.last_seen')
-> get();
I have only 2 users currently (admin, and user). If I try to view the friends of admin, I see admin (and that is not right, i would like to see user on admin friends and admin on user friends).
I have also tried to use joins inside where clauses, but I found out that doesn't work. I Could do two queries like so, but I would like 1 query only:
$friends1 = DB::table('friends') -> where('friends.user_id_sender', $user_id)
-> join('profiles', 'profiles.user_id', '=', 'friends.user_id_receiver')
-> join('users', 'users.id', '=', 'friends.user_id_receiver')
-> select('users.name', 'profiles.picture', 'users.id')
-> get();
$friends2 = DB::table('friends') -> where('friends.user_id_receiver', $user_id)
-> join('profiles', 'profiles.user_id', '=', 'friends.user_id_sender')
-> join('users', 'users.id', '=', 'friends.user_id_sender')
-> select('users.name', 'profiles.picture', 'users.id')
-> get();
How would I do this?
My database table that contains all the friendships looks like this:
