Laravel querying to get data from a table using another table and an input-CodePudding

So for context have 3 tables.

borrowers table

    protected $table = 'borrowers';
    protected $primaryKey = 'id';
    protected $fillable = ['borrower_name', 'IC', 'phone_no', 'address'];

books table

    protected $primaryKey = 'id';
    protected $fillable = ['ISBN', 'year', 'book_title', 'author', 'publisher_name', 'category'];

and finally borrow table with id, borrower_id, book_id , issue_date, return_date and late_return_status which is a bool. borrower_id and book_id are foreign keys.

The user also performs a search to choose which borrower's borrowed books to show. I've gotten the borrower's ID and stored it in a variable $id.

So i need to query to get all info from books table on books a borrower has borrowed issue_date, return_date and late_return_status.

Below is what ive tried so far.

  $books_borrowed = Borrow::join('books', 'borrow.book_id', '=', 'books.id')
                                        ->where('borrow.borrower_id', '=', $id)
                                        ->get(['books.ISBN', 'books.book_title','books.year' ,'books.author', 'books.publisher_name', 
                                        'borrow.issue_date', 'borrow.due_date', 'borrow.late_return_status']);

CodePudding user response：

I believe that another join is missing to get both the borrowers and the books tables linked to the foreign keys?

$books_borrowed = Borrow::join('books', 'borrow.book_id', '=', 'books.id')
    ->join('borrowers', 'borrow.borrower_id', '=', 'borrowers.id')
    ->where('borrow.borrower_id', '=', $id)
    ->get(['books.ISBN', 'books.book_title', 'books.year','books.author', 'books.publisher_name',
           'borrow.issue_date', 'borrow.due_date', 'borrow.late_return_status']);

This way, you have all 3 tables joined:

--------------                  ----------
 borrowers.id                    books.id
--------------                  ----------
      |                               |
      |    --------------------       |
      |---> borrow.borrower_id        |
            borrow.book_id     <------|  (you have this join)
           --------------------