2

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?

Lucid Polygon
  • 542
  • 9
  • 26
  • Transaction::where('contact_id',$id) ->with('allocations') ->whereHas("allocations",function ($query){ $query->havingRaw('transactions.amount>sum(amount)')->groupBy('transaction_id'); }) ->get(); – John Lobo Jun 19 '21 at 12:02
  • Tried the query and the following error occurs. Error in next comment. – Lucid Polygon Jun 19 '21 at 12:14
  • SQLSTATE[42000]: 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.amount > sum(amount))) – Lucid Polygon Jun 19 '21 at 12:15
  • try once 'strict' => false, in database.php for mtsql – John Lobo Jun 19 '21 at 12:15
  • tried. it gave me empty. supposed to give one result. – Lucid Polygon Jun 19 '21 at 12:20
  • then check once commenting having raw – John Lobo Jun 19 '21 at 12:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/233957/discussion-between-thaha-and-john-lobo). – Lucid Polygon Jun 19 '21 at 12:21

1 Answers1

3

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

John Lobo
  • 14,355
  • 2
  • 10
  • 20
  • 1
    this answer requires 'strict'=>false. Following answer explains how to avoid strict => false. https://stackoverflow.com/a/68047726/6684758 – Lucid Polygon Jun 20 '21 at 00:11
  • Tried to use this query and getting error: `SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cargo.application_transfers.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Андрей Федотов Nov 09 '21 at 09:34