Laravel query where column value is greater than sum of related model column values

Question

I have two tables transactions and transaction_allocations. One transaction can have many allocations.

Transaction Model

Schema::create('transactions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
            $table->foreignId('contact_id')->constrained();
            $table->decimal('amount',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);
    }

I need to query all transactions where the transaction amount is greater than the sum of all allocated amounts for that transaction. (Basically finding transaction which has unallocated balances).

Transaction::where('contact_id',$id)->where('amount','>',sum of allocations)->get();

How do I achieve it?

I was able to create an accessor to do this calculation and find an unallocated amount. But seems like accessors cannot be used in where. I don't want to load all transactions and then filter it as it will be too much.

I want to query and get filtered lines directly. How do I do it?

Answer 1

I think Having Raw is better to compare two column.

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

if query fails then try to 'strict' => false in database.php for mysql connection