Laravel Eloquent query with relationship table with where and wherehas

Question

Following query is ignoring the where('contact_id',$id) causing to return results that does not belong to this contact. Why?

Transaction::where('contact_id',$id)
            ->with('allocations')
            ->whereHas("allocations",function ($query){
            $query->select('transaction_id')
                ->havingRaw('transactions.amount > sum(amount)')
                ->groupBy('transaction_id');
            })
            ->orDoesntHave("allocations")
            ->get();

if I remove the whereHas part, it is bringing back the correct result. But I need the whereHas part.

            ->whereHas("allocations",function ($query){
            $query->select('transaction_id')
                ->havingRaw('transactions.amount > sum(amount)')
                ->groupBy('transaction_id');
            })
            ->orDoesntHave("allocations")

Basically, I am trying to query for transactions where the transaction amount is greater than the sum of allocations (transaction hasMany allocations) for that transaction.

Answer 1

I would say that the problem is this line

->orDoesntHave("allocations")

because the logic of your condition looks like this (in SQL)

WHERE contact_id = ... AND count_subquery_allocations > 0 OR count_have_no_allocations = 0

as you can see that OR "makes the condition true" even if contact_id = ... is false... you probably want this:

Transaction::where('contact_id',$id)
    ->where(function($query){
        return $query->whereHas("allocations",function ($query){
                return $query->select('transaction_id')
                    ->havingRaw('transactions.amount > sum(amount)')
                    ->groupBy('transaction_id');
            })
            ->orDoesntHave("allocations");
    })
    ->with('allocations')
    ->get();