Return a Builder whose every relation's model matches a specific condition

user_10072021 0
Viewed 56

I have a Order model and a Transaction model. A order can have one or multiple transactions.

I want those orders whose every transactions status are 'Pending'.

I tried to implement like below:

$builder = Order::with('transactions')->latest('id');

if(condition)
{
    $builder->whereHas('transactions', function ($query) {
        $query->where('status', 'Pending');
    });
}

This returns those order whose at least one transaction has 'Pending' status. But I want those orders whose every transactions have status 'Pending'.

laravel-8 eloquent laravel
2 Answers

I think you can do it easily using the opposite way, like:

$builder = Order::with('transactions')->latest('id');

if($condition)
{
    $builder = $builder->has('transactions')
        ->whereDoesntHave('transactions', function ($query) {
            $query->where('status','!=', 'Pending');
        });
}

$result = $builder->get();

the 'has' method insure that there is transactions, and whereDoesntHave insure that all transactions are in pending state

user_105735600

To find orders whose all of the transactions are marked as pending, you will need count of pending transactions by using conditional aggregation in withCount and then check if the total count of transactions is equal to pending count of transactions

Order::withCount([
    'transactions',
    'transactions as pending_count' => function ($query) {
        $query->select(DB::raw("sum(case when status = 'pending' then 1 else 0 end)"));
    }
])
->with('transactions')
->havingRaw('transactions_count = pending_count')
->get();
user_8533600
Related