Query with orWhere returns invalid elements

Hello. I have a 'Friends' table in my database and it stores ids of 2 users and accepted field ( if user confirmed invitation ). Now I want to remove specific friendship and my query is:

Friend::where([
            'user_id' => $friend_id,
            'friend_id' => Auth::id(),
            'accepted' => 1,
        ])->orWhere([
            'user_id' => Auth::id(),
            'friend_id' => $friend_id,
            'accepted' => 1,
        ])->get();

I used get() method to see what is wrong with it and it returns all records from DB instead of single element. And I need to use orWhere, because in table I don't want to 'duplicate' information about friendships ( if user 1 and user 2 are friends then I don't need to create reversed row - user 2 and user 1) What is wrong with this code?

Snapey

8 years ago

Level 122

I think you probably end up with an orWhere accepted=1 which is then the majority of the records?

try rearranging it because you want;

(pseudo)

WHERE accepted=1
AND 
    (where user_id = Auth::id() AND friend_id= $friend_id)
OR
    (where user_id = $friend_id AND friend_id = Auth::id() )

I would try;

Friend::where('accepted',1)
    ->where(function($q) use ($friend_id){
        $q->where('user_id', Auth::id());
        $q->where('friend_id', $friend_id);
    })
    ->orWhere(function($q) use ($friend_id){
        $q->where('friend_id', Auth::id());
        $q->where('user_id', $friend_id);
    })
    ->get();

1 like

Snapey

8 years ago

Best Answer

Level 122

Revised version

Friend::where(function($q) use ($friend_id){
        $q->where('user_id', Auth::id());
        $q->where('friend_id', $friend_id);
        $q->where('accepted',1);
    })
    ->orWhere(function($q) use ($friend_id){
        $q->where('friend_id', Auth::id());
        $q->where('user_id', $friend_id);
        $q->where('accepted',1);
    })
    ->get();

1 like

pmieleszkiewicz

8 years ago

Level 3

Thank You for Your replies. I saw a similar solution to Yours, but I've already solved this in different way.

Friends::where([
['user_id', '=', $friend_id],
['friend_id', '=', Auth::id()],
['accepted', '=', 1],
])->orWhere([
['user_id', '=', Auth::id()],
['friend_id', '=', $friend_id],
['accepted', '=', 1],
])->get();

Just passing arrays to where and orWhere clauses.

Please or to participate in this conversation.