withSum In some cases, the result is null. Can it be replaced with 0?

[samehdoush]

In some cases the result is false because the value is null
ex:
Orders Table :

          $table->id();
            $table->unsignedBigInteger('user_id')->nullable();
            $table->decimal('total')->default(0);
            .......... etc

   public function payments()
    {
        return $this->hasMany(Payment::class, 'order_id');
    }

Payments Table :

  $table->id();
            $table->unsignedBigInteger('order_id')->nullable();
            $table->decimal('price')->nullable();
            $table->text('token_id')->nullable();
            $table->string('payment_method')->default('bank')->nullable();
               .......... etc

in OrderController I want to filter the results and show only orders that have a remaining amount

 Order::withSum('payments as client_payments', 'price')->havingRaw('total > client_payments')->get(); // not working return 0 item

 Order::withSum('payments as client_payments', 'price')->havingRaw('total > IFNULL(client_payments, 0)')->get(); // working return 5 item
 
 

Is there a way to make withSum return 0 permanently if it is null?

[Sophist-UK]

This is clearly an issue with the SQL that is being generated. Can you show us the SQL from the two statements in the final example?

[samehdoush]

 Order::withSum('payments as client_payments', 'price')->havingRaw('total > client_payments')->toSql();


"select `orders`.*, (select sum(`payments`.`price`) from `payments` where `orders`.`id` = `payments`.`order_id` and `payments`.`deleted_at` is null) as `client_payments` from `orders` having total > client_payments ◀" // app\Http\Controllers\Web\Admin\OrderController.php:51

 Order::withSum('payments as client_payments', 'price')->havingRaw('total > IFNULL(client_payments, 0)')->toSql();


"select `orders`.*, (select sum(`payments`.`price`) from `payments` where `orders`.`id` = `payments`.`order_id` and `payments`.`deleted_at` is null) as `client_payments` from `orders` having total  > IFNULL(client_payments, 0)◀" // app\Http\Controllers\Web\Admin\OrderController.php:51

without havingRaw

 Order::withSum('payments as client_payments', 'price')->limit(3)->get();

 0 => array:27 [▼
   
    "price" => 2
    "client_payments" => null // here 
  ]
 1 => array:27 [▼
   
    "price" => 2
    "client_payments" => null // here 
  ]
 2 => array:27 [▼
   
    "price" => 2
    "client_payments" => 2
  ]

[Sophist-UK]

My suggestion would be to make $table->decimal('price')->default(0); instead.

(I cannot tell whether you are getting a null because there are no records or because all records are null or because at least one record is null and it is overriding a sum of the other records which have values.)