Laravel中多条件统计Transaksi表金额结果为0的异常排查

Your $kredit result returns 0 despite existing matching data due to one or more of these issues:

Root Causes #

1. Collection Filtering Instead of Database Query: You’re fetching all records first with get(), then filtering in memory. This is inefficient and can lead to unexpected results if the initial query misses records (e.g., due to date filtering errors).
2. Date Filter Mismatch: Using whereDate with datetime objects (from startOfDay()/endOfDay()) on a date column can cause timezone or casting issues, excluding valid records.
3. Invalid Schema Definition: The provided schema has a broken nasabah_id line ($table->('nasabah_id');). If the actual column type doesn’t match $id (e.g., string vs integer), the nasabah_id filter won’t match records.

Corrected Code #

Best Practice: Database-Level Sum #

Calculate the sum directly in the database by including all filters in the query builder. This is faster and avoids collection-related bugs:

$start = Carbon::parse($request->input('start'))->toDateString();
$end = Carbon::parse($request->input('end'))->toDateString();

$kredit = Transaksi::whereBetween('tgl_trans', [$start, $end])
    ->where('nasabah_id', $id)
    ->where('jenis_id', 1)
    ->sum('nominal');

If You Need the Full Collection #

If you require the collection for other operations, fix the date filter to use date strings instead of datetime objects:

$start = Carbon::parse($request->input('start'))->toDateString();
$end = Carbon::parse($request->input('end'))->toDateString();

$query = Transaksi::where('tgl_trans', '>=', $start)
    ->where('tgl_trans', '<=', $end)
    ->where('nasabah_id', $id)
    ->get();

$kredit = $query->where('jenis_id', 1)->sum('nominal');

Fix the Database Schema #

Ensure nasabah_id is properly defined (assuming it references a nasabahs table):

Schema::create('transaksis', function (Blueprint $table) {
    $table->id();
    $table->string('kode_trans');
    // Correct nasabah_id definition with foreign key
    $table->unsignedBigInteger('nasabah_id');
    $table->foreign('nasabah_id')->references('id')->on('nasabahs')->onDelete('cascade')->onUpdate('cascade');
    $table->decimal('saldo_awal', 15, 2)->nullable();            
    $table->unsignedBigInteger('jenis_id');
    $table->foreign('jenis_id')->references('id')->on('jenis')->onDelete('cascade')->onUpdate('cascade');
    $table->decimal('nominal', 15, 2)->nullable();
    $table->decimal('saldo', 15, 2)->nullable();
    $table->integer('diff_day')->nullable();
    $table->unsignedBigInteger('user_id');
    $table->date('tgl_trans');
    $table->timestamps();
});

Key Checks #

• Verify that $id matches the nasabah_id values in your database (check data types: integer vs string).
• Confirm that jenis_id=1 exists for the target records.
• Ensure timezone settings in Laravel and your database are aligned to avoid date shifting.

内容的提问来源于stack exchange，提问作者PS _94