php - Laravel unique validation on multiple column

Question

I have a table that defines user connections like facebook friend. It has fields like -

id------------ sender_id ---------- receiver_id ---------------- status

Now, I want to validate if same data exists between 2 columns. Means,

1 ------------- 11 ---------- 22 -------- status if this row exists, then

1 ------------- 11 ---------- 22 -------- status this shouldn't be there. also

1 ------------- 22 ---------- 11 -------- status this shouldn't be there.

Here, the sender_id is the auth user id.

I am trying to validate the receiver_id

$thisUser = auth()->user();

$validator = Validator::make($request->all(), [
            'receiver_id' => [
                'required', 
                'exists:users,id',
                Rule::unique('user_connections')->where('sender_id', $thisUser->id),
                function($attribute, $value, $fail) use($thisUser, $request) {
                    if($thisUser->id == $request->input('receiver_id')) {
                        return $fail('You cannot send request to own!');
                    }
                },
            ],
        ]);

question from:https://stackoverflow.com/questions/65872327/laravel-unique-validation-on-multiple-column

Answer 1

In this case your query will be checking both columns 2 times with OR criteria like

Lets say

• :sender_id = 11
• :receiver_id = 22

first clause will match sender_id with 11 and receiver_id with 22 and followed by another same clause but will swap values for these columns as sender_id with 22 and receiver_id with 11, so query will look like as

select count(*)
from user_connections
where (sender_id = :sender_id and receiver_id = :receiver_id)
   or (sender_id = :receiver_id and receiver_id = :sender_id)

You can customize your unique rule by extending your query as

Rule::unique('user_connections')->where(function ($query) use($thisUser, $request) {
    return $query->where(function ($query) use($thisUser, $request) {
               $query->where('sender_id', $thisUser->id)
                     ->where('receiver_id', $request->input('receiver_id'));
           })->orWhere(function($query) use($thisUser, $request){
                $query->where('sender_id', $request->input('receiver_id'))
                     ->where('receiver_id', $thisUser->id);
            })
})