Laravel Database filter total greater than and using distinct-CodePudding

How can I filter out only one results when total payment is greater than bill?

I tried 3 ways like this but still didn't work

Thank you

1.trial to one (not working)

$paid_off= Instalments::distinct()->whereRaw('bills >= payments ')->get();

2.second try (not working)

$paid_off = DB::table('instalments')
        ->selectRaw('count(*) as badanusaha_id, badanusaha_id')
        ->where(('Sum(bills )'), '>=', ('Sum([payments][1] )'))
        ->groupBy('badanusaha_id')
        ->distinct()
        ->get();

3.third try (not working)

$pem1 = Instalments::distinct(DB::raw("CAST(SUM(bills) as int) as pem1"));
$pem2 = Instalments::select(DB::raw("CAST(SUM(payments ) as int) as pem2"));
        
        if($pem1 >= $pem2){
            $paid_off = Instalments::distinct()->select('*')->get();
        }

image

from this image, only show one data that is PT Mandiri because total payments grather than total bills

CodePudding user response：

I don't know what you're trying to achieve here. but from the last statement you mentioned

only show one data that is PT Mandiri because total payments grather than total bills

this query will work as expected:

$instalments = Instalments::query()
   ->whereRaw('payments >= bills')
   ->groupBy('badanusaha_id')
   ->get();

result:

array:1 [▼
  0 => {#1357 ▼
     "id": 1
     "badanusaha_id": 45
     "bills": "100.00"
     "payments": "100.00"
  }
]