如何基于Laravel模型查询用户可借阅的有库存书籍列表？

Problem Statement #

I need help with a query to find books available for borrowing. I want to prevent users from borrowing books that are either already checked out (not returned yet) or have insufficient stock.

• I have a Book model and books table with a number_of_copy field.
• I have a BorrowRequest model and borrow_requests table storing borrower/user details.
• I have a BorrowRequestItem model and borrow_request_items table, which includes a quantity field (number of books the user is borrowing) and a returned_at field (marking if the book has been returned).

Original Code Issue #

Your initial code has a scope problem: the subquery tries to reference books.number_of_copy in a HAVING clause, which isn't accessible from within that subquery. This will cause a database error. Additionally, the logic doesn't properly account for total unreturned copies across all users, only the current user's borrows.

Fixed Code #

$user = 1; // dynamic user ID

if (!$user) {
    return [];
}

$availableBooks = \App\Models\Book::select('books.id', 'books.title')
    ->where('books.number_of_copy', '>', 0)
    ->where('books.is_requestable', true)
    ->whereNull('books.deleted_at')
    // Calculate total unreturned copies of each book across all users
    ->leftJoinSub(
        function ($query) {
            $query->select('book_id', DB::raw('SUM(quantity) as total_unreturned'))
                ->from('borrow_request_items')
                ->join('borrow_requests', 'borrow_request_items.borrow_request_id', '=', 'borrow_requests.id')
                ->whereIn('borrow_requests.status', ['Pending', 'Approved'])
                ->whereNull('borrow_request_items.returned_at')
                ->groupBy('book_id');
        },
        'unreturned',
        'unreturned.book_id',
        '=',
        'books.id'
    )
    // Calculate current user's unreturned copies of each book
    ->leftJoinSub(
        function ($query) use ($user) {
            $query->select('book_id', DB::raw('SUM(quantity) as user_unreturned'))
                ->from('borrow_request_items')
                ->join('borrow_requests', 'borrow_request_items.borrow_request_id', '=', 'borrow_requests.id')
                ->where('borrow_requests.borrower_user_id', $user)
                ->whereIn('borrow_requests.status', ['Pending', 'Approved'])
                ->whereNull('borrow_request_items.returned_at')
                ->groupBy('book_id');
        },
        'user_unreturned',
        'user_unreturned.book_id',
        '=',
        'books.id'
    )
    // Ensure there are available copies left after all unreturned borrows
    ->whereRaw('(books.number_of_copy - COALESCE(unreturned.total_unreturned, 0)) > 0')
    // Optional: Prevent user from re-borrowing books they already have unreturned
    ->whereNull('user_unreturned.book_id')
    ->pluck('title', 'id')
    ->all();

return $availableBooks;

Key Improvements #

1. Resolved Scope Error: Used leftJoinSub to pre-calculate unreturned counts, allowing proper reference to book stock values.
2. Total Stock Check: The query now accounts for all unreturned copies across all users, not just the current user's, to ensure actual available stock.
3. User-Specific Restriction: The second subquery checks if the user already has unreturned copies of the book. If you want to allow additional borrows (as long as stock permits), replace the whereNull('user_unreturned.book_id') line with:

   ->whereRaw('(books.number_of_copy - COALESCE(unreturned.total_unreturned, 0)) > COALESCE(user_unreturned.user_unreturned, 0)')
   

4. Null Safety: COALESCE ensures books with no unreturned copies are handled correctly (count defaults to 0).

Content sourced from Stack Exchange, question author: Jefferson Sarmiento