I’m facing an issue while trying to retrieve entries using Eloquent that meet specific criteria involving filtering on two related models. The goal is to first select reservations with a "Checked-In" status and then further filter these reservations to only include those with associated receipts in the "UNPAID" status. However, I’m encountering unexpected behavior where, if a reservation has at least one unpaid receipt, all receipts are returned, even if some of them have a "PAID" status

$reservations = Reservation::select('reservations.*')
    ->where('status', 'Checked-In')
    ->whereHas('bill.receipts', function ($query) {
        $query->where('status', 'UNPAID');
    })
    ->get();

$reservations = Reservation::with(['bill.receipts.foodOrders.food', 'rooms'])
    ->where('status', 'Checked-In')
    ->whereHas('bill', function ($query) {
        $query->whereHas('receipts', function ($subquery) {
            $subquery->where('status', 'UNPAID');
        });
    })
    ->get();

Both snippets are supposed to retrieve reservations with a "Checked-In" status and filter out those that have associated receipts with a "PAID" status. However, the issue arises when a reservation has one unpaid receipt; in that case, all receipts are returned, regardless of their status.

I suspect that my approach might be incorrect or that there might be some unintended interactions between the models and relationships

I’ve tried multiple whereHas() or double checking the relationships. Everything seems look alright. I feel I am missing something

For this following code, I started with Receipts instead:

$receipts = Receipt::with(['bill.reservation', 'foodOrders.food'])
    ->whereHas('bill.reservation', function ($query) {
        $query->where('reservationStatus', 'Checked-In');
    })
    ->where('status', 'UNPAID')
    ->get();

It does work and perhaps this a much better practice in terms of intuition and readability since I essentially want to display Receipts. But I cannot shake the feeling that the same can be done when starting with Reservation

>Solution :

This works as expected, but when you are going through the relations, it will reload them without considering the whereHas() conditionals, or any other for that matter.

Do what you are already doing, but use with() to filter the relationships also.

// previous calls but dont call ->get() yet
->with(['bill.receipts' => function ($query) {
    $query->where('status', 'UNPAID');
}])
->get();