Laravel orWhere is causing other where queries to be ignored-CodePudding

I have to output data using 3 different tables and they have to meet certain conditions.

->where('borrows.late_return_status', '=', 1)
->where('borrows.return_date', '=', null)

But also, i need to allow the user to search through the list using name or IC.

->where('borrowers.borrower_name', 'like', '%'.$search.'%')
->orWhere('borrowers.IC', 'like', '%'.$search.'%')

My issue is that, when the orWhere condition is met, even the necessary conditions are overlooked and it shows data without late_return_status as 1.

Here's the whole code.

$late_books = Borrow::join('books', 'borrows.book_id', '=', 'books.id')
                                        ->join('borrowers', 'borrows.borrower_id', '=', 'borrowers.id')
                                        ->where('borrowers.borrower_name', 'like', '%'.$search.'%')
                                        ->orWhere('borrowers.IC', 'like', '%'.$search.'%')
                                        ->where('borrows.late_return_status', '=', 1)
                                        ->where('borrows.return_date', '=', null)
                                        ->get(['borrowers.borrower_name', 'borrowers.IC', 'borrowers.phone_no' ,'books.ISBN', 'books.book_title', 'books.year','books.author', 'books.publisher_name',
                                            'borrows.issue_date', 'borrows.due_date']);

CodePudding user response：

What you want is to enclosed the search conditions in a parenthesis.

You can achieve this using Logical grouping (https://laravel.com/docs/9.x/queries#logical-grouping).

$late_books = Borrow::join('books', 'borrows.book_id', '=', 'books.id')
    ->join('borrowers', 'borrows.borrower_id', '=', 'borrowers.id')
    ->where('borrows.late_return_status', '=', 1)
    ->where('borrows.return_date', '=', null)
    ->where(function ($query) use ($search) {
      $query->where('borrowers.borrower_name', 'like', '%'.$search.'%')
        ->orWhere('borrowers.IC', 'like', '%'.$search.'%')
    })
    ->get(['borrowers.borrower_name', 'borrowers.IC', 'borrowers.phone_no' ,'books.ISBN', 'books.book_title', 'books.year','books.author', 'books.publisher_name',
           'borrows.issue_date', 'borrows.due_date']);