Clever Geek Handbook

Left Join search results return bad results

I've been trying for hours to make a left-hand MySQL connection query.
I have a table called "at_friends" where I store the relationship between users.

id  |  id_user1 |  id_user2  |  accepted
1   |  2        |  1         |  1
2   |  1        |  3         |  0

This means that user 1 is a friend with user 2, and user 1 has sent a request to a friend of user.

And here is my table "at_users" 

id  |  name
1   |  "John"   
2   |  "Mike"   
3   |  "Bob"

I tried this query:

SELECT at_users.id, at_users.name
    FROM at_users 
    LEFT JOIN at_friends
         ON at_friends.id_user1 = at_users.id
         OR at_friends.id_user2 = at_users.id
         AND at_friends.accepted = 1
    WHERE id_user1 = 1 OR id_user2 = 1

"1" - unique identifier of the current user (John)


But I am getting these results

id  |  name
1   |  "John"   
2   |  "Mike"   
1   |  "John"

What I'm trying to get is user n ° 1's accepted friends list.

By the way, I'm using Laravel, but I don't know if there is a way to do this without a special mysql query.

+3


source to share


3 answers


Try the following:



SELECT u.id, u.name
FROM at_users u 
INNER JOIN (SELECT id_user2 AS friendID FROM at_friends WHERE id_user1 = 1 AND accepted = 1
            UNION 
            SELECT id_user1 AS friendID FROM at_friends WHERE id_user2 = 1 AND accepted = 1
           ) AS A ON u.id = A.friendID
+3


source


An alternative that only requires one scan of the friends table:

SELECT u.id, u.name
FROM at_users u 
JOIN at_friends f
  ON 1 in (f.id_user1, f.id_user2) and
     u.id = case f.id_user1 when 1 then id_user2 else id_user1 end and 
     f.accepted=1


SQLFiddle here.

+2


source


I know this question already has an accepted answer, but I wanted to add this ...

There is a Laravel way!

Add this helper method to the model User 

public function friends(){
    $id = $this->id;
    return $this->join('at_friends', function($q) use ($id){
        $q->on('at_friends.id_user2', '=', 'users.id');
        $q->where('at_friends.id_user1', '=', $id);
        $q->orOn('at_friends.id_user1', '=', 'users.id');
        $q->where('at_friends.id_user2', '=', $id);
    });
}

This is how you can use it

$user = User::find(1);
$user->friends()->where('accepted', true)->get();

If you want to be able to add this attribute to the model:

public function getFriendsAttribute(){
    return $this->friends()->where('accepted', true)->get();
}

And use it like this:

$user->friends;
+2


source






More articles:


All Articles