Division by Zero on paging enabled

reiki

Is there some part of paging that cause "Exception Message: Division by zero" ? i cant find what cause this issue, its only and only happen when paging enabled and going to page other than the first page (first page display table no problem), totalRecords and totalFilteredRecords were correct on first page

colin

I've never seen that issue before. What does the server return when you see that error? Are you able to link to your page so we can debug?

Colin

reiki

Its not possible to link to the page because its still in my local, the error i see is just this
and this is on 1st page which is working fine

reiki

my query does not have any division this is the query

$scoreboard = HistoryPenjualan::select(DB::raw('*'));                     
                      $scoreboard->from(function($query) use ( $hariSama, $tglA, $tglB, $kodeMerk, $barangId, $orderColumn, $orderDir, $columns ){
                        $query ->selectRaw(DB::raw("DISTINCT DATE_FORMAT(`tgl`, '%Y-%m') as 'tgl1',`departemenId`,  SUM(score) as totalScore"));
                        for ($j = 0; $j < 31; $j++){
                          $dateFormat = isset($hariSama[$j]) ? $hariSama[$j] : 0;
                            $query->selectRaw(DB::raw("(SUM(CASE WHEN tgl IN ($dateFormat) THEN score END) ) AS 'Hari$j' "));
                        };
                        if (!empty($kodeMerk)){
                          $query->whereRaw(DB::raw("EXISTS (SELECT * FROM `barang` where `history_penjualan`.`barangId` = `barang`.`id` and `kodeMerk` = '$kodeMerk')"));
                        }
                        if (!empty($barangId)){
                          $query->where('barangId', $barangId);
                        }
                        $query->whereBetween('tgl', [$tglA, $tglB])  
                        ->groupBy('tgl1')          
                        ->groupBy('departemenId');
                        if(!empty($orderDir) && $orderColumn != 0){
                          $query->orderBy($columns[$orderColumn],$orderDir);
                        } 
                        $query->from('history_penjualan');
                      });   
                $scoreboard2 = $scoreboard->count();
                $scoreboard->skip($start)->take($length)
                ->get();

its dynamic query and the result will look like this :

select * from (select DISTINCT DATE_FORMAT(`tgl`, '%Y-%m') as 'tgl1',`departemenId`,  SUM(score) as totalScore, (SUM(CASE WHEN tgl IN ('2023-01-01') THEN score END) ) AS 'Hari0' , (SUM(CASE WHEN tgl IN ('2023-01-02') THEN score END) ) AS 'Hari1' , (SUM(CASE WHEN tgl IN ('2023-01-03') THEN score END) ) AS 'Hari2' , (SUM(CASE WHEN tgl IN ('2023-01-04') THEN score END) ) AS 'Hari3' , (SUM(CASE WHEN tgl IN ('2023-01-05') THEN score END) ) AS 'Hari4' , (SUM(CASE WHEN tgl IN ('2023-01-06') THEN score END) ) AS 'Hari5' , (SUM(CASE WHEN tgl IN ('2023-01-07') THEN score END) ) AS 'Hari6' , (SUM(CASE WHEN tgl IN ('2023-01-08') THEN score END) ) AS 'Hari7' , (SUM(CASE WHEN tgl IN ('2023-01-09') THEN score END) ) AS 'Hari8' , (SUM(CASE WHEN tgl IN ('2023-01-10') THEN score END) ) AS 'Hari9' , (SUM(CASE WHEN tgl IN ('2023-01-11') THEN score END) ) AS 'Hari10' , (SUM(CASE WHEN tgl IN ('2023-01-12') THEN score END) ) AS 'Hari11' , (SUM(CASE WHEN tgl IN ('2023-01-13') THEN score END) ) AS 'Hari12' , (SUM(CASE WHEN tgl IN ('2023-01-14') THEN score END) ) AS 'Hari13' , (SUM(CASE WHEN tgl IN ('2023-01-15') THEN score END) ) AS 'Hari14' , (SUM(CASE WHEN tgl IN ('2023-01-16') THEN score END) ) AS 'Hari15' , (SUM(CASE WHEN tgl IN ('2023-01-17') THEN score END) ) AS 'Hari16' , (SUM(CASE WHEN tgl IN ('2023-01-18') THEN score END) ) AS 'Hari17' , (SUM(CASE WHEN tgl IN ('2023-01-19') THEN score END) ) AS 'Hari18' , (SUM(CASE WHEN tgl IN ('2023-01-20') THEN score END) ) AS 'Hari19' , (SUM(CASE WHEN tgl IN ('2023-01-21') THEN score END) ) AS 'Hari20' , (SUM(CASE WHEN tgl IN ('2023-01-22') THEN score END) ) AS 'Hari21' , (SUM(CASE WHEN tgl IN ('2023-01-23') THEN score END) ) AS 'Hari22' , (SUM(CASE WHEN tgl IN ('2023-01-24') THEN score END) ) AS 'Hari23' , (SUM(CASE WHEN tgl IN ('2023-01-25') THEN score END) ) AS 'Hari24' , (SUM(CASE WHEN tgl IN ('2023-01-26') THEN score END) ) AS 'Hari25' , (SUM(CASE WHEN tgl IN ('2023-01-27') THEN score END) ) AS 'Hari26' , (SUM(CASE WHEN tgl IN ('2023-01-28') THEN score END) ) AS 'Hari27' , (SUM(CASE WHEN tgl IN ('2023-01-29') THEN score END) ) AS 'Hari28' , (SUM(CASE WHEN tgl IN ('2023-01-30') THEN score END) ) AS 'Hari29' , (SUM(CASE WHEN tgl IN ('2023-01-31') THEN score END) ) AS 'Hari30'  from `history_penjualan` where `tgl` between '2023-01-01' and '2023-01-31' group by `tgl1`, `departemenId`) as `` limit 10 offset 10

reiki

UPDATE

I am using pipeline to cache all page and its working now but its no use cause i want to still ajax call every page to minimize query usage (its too long of process now) so i guess the paging it self isnt an issue because i can get it work by pipelining but the Ajax call of paging issue is the problem

kthorngren

If the division isn't taking place in the query maybe it happens somewhere else in your server script. Are you using a Datatables supplied server script or something else?

Kevin