php - 如何在 laravel 上正确分组--SQLSTATE[42803]: Grouping error: 7 ERROR

Question

请帮助我，我有这样的代码，但代码组中存在问题

 public function table(Request $request)
{
    $order = $request->input('columns');
    $tahun_st = date('Y');

    if($order[9]['search']['value']){
        $tahun_st = $order[9]['search']['value'];
    }

    if($tahun_st == null || $tahun_st == ''){ 
        $tahun_st = date('Y');
    }

    $data =  DB::table('pegawais')->select('pegawais.*')
                        ->selectRAW('sum(surat_tugas_nominatifs.jml_hari) as jumlah_hari')
                        ->selectRAW('sum(surat_tugas_nominatifs.tiket + surat_tugas_nominatifs.taksi_tujuan + surat_tugas_nominatifs.taksi_awal + surat_tugas_nominatifs.trans_short_dist + surat_tugas_nominatifs.transport_lokal :: DOUBLE PRECISION)  AS transport')
                        ->selectRAW('sum(COALESCE(surat_tugas_nominatifs.lama_inap * surat_tugas_nominatifs.cost_inap)) as penginapan')
                        ->selectRAW('SUM (CASE WHEN surat_tugass.jenis_st_id = 5 THEN COALESCE (surat_tugas_nominatifs.uang_rapat * (( 100 - surat_tugas_nominatifs.pajak ) / 100 :: DOUBLE PRECISION )) WHEN surat_tugass.jenis_st_id = 2 THEN COALESCE ((surat_tugas_nominatifs.fb + surat_tugas_nominatifs.fd ) * (( 100 - surat_tugas_nominatifs.pajak ) / 100 :: DOUBLE PRECISION )) WHEN surat_tugass.jenis_st_id = 3 THEN surat_tugas_nominatifs.fb_luar_kota ELSE surat_tugas_nominatifs.jml_hari * surat_tugas_nominatifs.harian END) AS harian')
                        ->leftjoin('surat_tugas_pesertas','surat_tugas_pesertas.pegawai_id', 'pegawais.id')
                        ->leftJoin('surat_tugas_nominatifs', function($join) {
                            $join->on('surat_tugas_nominatifs.peserta_id', 'surat_tugas_pesertas.id')
                                ->where('surat_tugas_nominatifs.jml_hari', '!=' , 0);
                        }) 
                        ->leftJoin('surat_tugass','surat_tugas_pesertas.surat_tugas_id', 'surat_tugass.id')
                        ->leftJoin('view_latest_jabatan_pegawai', 'view_latest_jabatan_pegawai.pegawai_id', 'pegawais.id')
                         ->whereRAW("date_part('year', surat_tugass.tgl_awal) = ". $tahun_st)
                        ->where('surat_tugass.cetak_stat', true)
                        ->where('surat_tugass.jenis_st_id', '!=', 10)//jika bukan narasumberr
                        ->groupBy('pegawais.id');
    if(!auth()->user()->can('rekap surat tugas all')){
        $unit_kerja_org =  DB::table('ktus')->select('*')->where('pegawai_id', auth()->user()->pegawai_id)->first();
        // dd($unit_kerja_org);
        $data = $data->where('view_latest_jabatan_pegawai.org_unit_kerja_id', $unit_kerja_org->organisasi_id);
    }

    $action = [];

    $dataTable = Datatable::create($data)
        ->setId('pegawais.id')
        ->editColumn('action', function ($data) use($request, $tahun_st) {  
            return '<a title="Manage" href="'.route($this->route_list.'.list', ['id' => $data->id, 'tahun' => $tahun_st]).'" class="btn shoot btn-icon-only blue tooltips">
                    <i class="fa fa-tasks"></i>
                </a>';
        })
        ->editColumn('pegawai_nama', function ($data) use($request) {
            return $data->nama;
        })
        ->filterColumn('tahun', function ($query, $key) {
            return $query->whereRAW("date_part('year', surat_tugass.tgl_awal) = ".$key);
        })
        ->editColumn('tahun', function ($data) use($request) {
        })
        ->editColumn('transport', function ($data) use($request) {
            if(Helper::convert_number($data->transport)){
                return  Helper::convert_number($data->transport);
            }else{
                return  '-';
            }
        })
        ->editColumn('uk', function ($data) use($request) {
            // return $data->organisasi_id;
        })
        ->editColumn('penginapan', function ($data) use($request) {
            if(Helper::convert_number($data->penginapan)){
                return  Helper::convert_number($data->penginapan);
            }else{
                return  '-';
            }
        })
        ->editColumn('harian', function ($data) use($request) {
            if(Helper::convert_rupiah($data->harian)){
                return  Helper::convert_number($data->harian);
            }else{
                return  '-';
            }
        })
        ->editColumn('jml_hari', function ($data) use($request) {
            if($data->jumlah_hari){
                $jumlah_hari = $data->jumlah_hari;
            }else{
                $jumlah_hari = 0;
            }
            return $jumlah_hari.' hari';
        })
        ->editColumn('total', function ($data) use($request) {
            $total = 0;
            $total = $data->penginapan + $data->harian + $data->transport;
            if($total != 0 ){
                return Helper::convert_number($total);
            }else{
                return  '-';
            }
        })
        ->defaultAction($action, ['route' => $this->route, 'title' => $this->title, 'param-model' => 'vendor']);

    if ($request->has('dateFilter')) {
        $date = Carbon::createFromTimestamp($request->input('dateFilter'));
    } else {
        $date = Carbon::now();
    }

    $response = $dataTable->make(true);
    return response()->json($response);
}

这里我有上面的代码，但是要编辑列的天数并且不能过滤总数。SQLSTATE [42803] 错误：分组错误：7 错误：列“surat_tugas_nominatifs.jml_day”必须出现在 GROUP BY 子句中或在聚合函数中使用第 1 行：... is_st_id “！= $ 3 group by “pegawais”。 "id" order by "jml_hari" ... ^ (SQL: select "pegawais". *, sum (surat_tugas_nominatifs.jml_hari) as NUMBER_DAY, sum (surat_tugas_nominatifs.tiket + surat_tugas_nominatifs.taksi_tuju + surat_tugas_nominatifs.jml_hari) 作为 Quantity_day, sum ( surat_tugas_nominatifs.tiket + surat_tugas_nominatifs.taksi_tujuan + surat_tugas_nominatifs.jml_hari) as Quantity_day, sum (surat_tugas_nominatifs.tiket + surat_tugas_nominatifs.taksi_tuju + surat_tugas_nominatifs.jml_hari) asas_day, 字母 sumas transport_lokal :: 双精度）作为运输，总和（COALESCE (surat_tugas_nominatifs.lama_inap * surat_tugas_nominatifs.cost_inap)）作为住宿，总和（当 surat_tugass.jenis_st_id = 5 时的情况，然后合并 (surat_tugas_nominatifs.*report) / 100 :: 双精度）） WHEN surat_tugass.type_st_id = 2 THEN COALESCE ((surat_tugas_nominatifs.fb + surat_tugas_nominatifs.fd) * ((100 - surat_tugas_nominatifs.pajak) / 100 :: DOUBLE PRECISION)) WHEN sEC urat_tugass.jenis_st_id = 3 THEN surat_tugas_nominatifs.fb_luar_kota ELSE surat_tugas_nominatifs.jml_hari * surat_tugas_nominatifs.harian END) AS 每日，“pegawais”。来自“pegawais”的“id”左加入“letters_certification_signs”“”“信”官员“官员”官员“官员”官员“官员”官员“官员” 官员“官员”官员“官员”官员“官员”。. “id”在“surat_tugas_nominatifs”上左加入“surat_tugas_nominatifs”。“particip_id”=“surat_tugas_pesertas”。“id”和“surat_tugas_nominatifs”。“jml_day”！= 0 在“surat_tugas_pesertas”上左加入“surat_tugasass”。“id”和“surat_tugas_nominatifs”。“天数”！“苏拉特图加斯”。“id”在“view_latest_jidang_pegawai”上离开加入“view_latest_jidang_pgawai”。“employee_id”=“pegawais”。"id" 其中 date_part ('year', surat_tugass.tgl_awal) = 2020 和 "surat_tugass"。1 和“surat_tugass”。“类型_st_id”！= 10组由“

标签： phplaravelpostgresql