Laravel query works with database.php strict=>false. How to make it work with strict=>true?

Question

There is a query that works when strict => false in database.php of Laravel. I don't want to make strict => false, I want it to remain true.

Is there a way to change this query and achieve the same results?

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

Error when enabling strict mode

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'transaction_allocations.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from transactions where contact_id = 9 and exists (select * from transaction_allocations where transactions.id = transaction_allocations.transaction_id and transaction_allocations.deleted_at is null group by transaction_id having transactions.credit > sum(amount)) or not exists (select * from transaction_allocations where transactions.id = transaction_allocations.transaction_id and transaction_allocations.deleted_at is null))

Context: There are two tables transactions and transaction_allocations. One Transaction can have many allocations.

I am trying to find transactions that are not allocated fully.

Meaning: get transactions where transaction.credit field is greater than sum of amounts for that transaction in related table(transaction_allocations).

Transaction Model

Schema::create('transactions', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->timestamps();
    $table->foreignId('contact_id')->constrained();
    $table->decimal('credit', 19, 4)->nullable();
});

Allocation Model

Schema::create('transaction_allocations', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->timestamp('created_at');
    $table->foreignId('transaction_id')->nullable()->constrained();
    $table->foreignId('bill_id')->nullable()->references('id')->on('bills');
    $table->decimal('amount', 19, 4)->nullable();
});

Relationship in Transaction Model

public function allocations()
{
    return $this->hasMany(TransactionAllocation::class);
}

Answer 1

GROUP BY statements in strict mode require that all the non-aggregate fields (COUNT, SUM, MAX, etc.) in the SELECT statement be present in the GROUP BY.

As shown in the error, the whereHas() method has produced a select * query, however, your GROUP BY statement only has transaction_id.

To fix this, you can simply add select('transaction_id') to your whereHas call:

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