Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How To Distinct By Year and GroupBy Category On Same Query?

I want to make a result from query after distinct and groupby
This is My Code

 $results = DB::table('dokumen')
            ->join('pengadaan', 'dokumen.id_jenis_pengadaan', '=', 'pengadaan.id')
            ->select(
                    DB::raw('COUNT(nama_paket) AS jml'),
                    DB::raw('jenis_pengadaan as jenis_pengadaan'),
                    DB::raw('tahun as tahun')
                    )
            ->groupBy('jenis_pengadaan', 'tahun')
            ->orderBy('tahun')
            ->get();

        $hasil = [];
        foreach ($results as $result) {
            $kategori = $result->jenis_pengadaan;
            $jml =  $result->jml;
                    $hasil[] = [
                        'tahun' => $result->tahun,
                        'kategori_paket' => $kategori,
                        'jml' => $jml,
                    ];
        }

dd($hasil);

And This is The Results of My Code

array:12 [▼ // app/Http/Controllers/HomeController.php:51
  0 => array:3 [▶
    "tahun" => 2022
    "kategori_paket" => array:1 [▶
      0 => "Pengadaan Langsung - Barang"
    ]
    "jml" => array:1 [▶
      0 => "9"
    ]
  ]
  1 => array:3 [▶
    "tahun" => 2022
    "kategori_paket" => array:1 [▶
      0 => "Pengadaan Langsung - Konstruksi"
    ]
    "jml" => array:1 [▶
      0 => "69"
    ]
  ]
  2 => array:3 [▶
    "tahun" => 2022
    "kategori_paket" => array:1 [▶
      0 => "Tender-Konstruksi"
    ]
    "jml" => array:1 [▶
      0 => "12"
    ]
  ]
  3 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "Pengadaan Langsung - Barang"
    ]
    "jml" => array:1 [▶
      0 => "30"
    ]
  ]
  4 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "Pengadaan Langsung - Jasa Lainnya"
    ]
    "jml" => array:1 [▶
      0 => "17"
    ]
  ]
  5 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "Pengadaan Langsung - Konstruksi"
    ]
    "jml" => array:1 [▶
      0 => "201"
    ]
  ]
  6 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "Tender-Konstruksi"
    ]
    "jml" => array:1 [▶
      0 => "25"
    ]
  ]
  7 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "Seleksi"
    ]
    "jml" => array:1 [▶
      0 => "5"
    ]
  ]
  8 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "e-Katalog"
    ]
    "jml" => array:1 [▶
      0 => "2"
    ]
  ]
  9 => array:3 [▶
    "tahun" => 2023
    "kategori_paket" => array:1 [▶
      0 => "Pengadaan Langsung-Konsultan"
    ]
    "jml" => array:1 [▶
      0 => "14"
    ]
  ]
]

How to group the $hasil only 2 array groupby Year? so there’s only 2022 and 2023

i’ve tried everything to solve this . but i cant find the answer
hope you guys cant help me solve this
thanks . best regards

Tags:

3

Answers


  1. 0

    You could modify your loop to collect together entries that have the same $result->tahun value:

    $hasil = [];
foreach ($results as $result) {
    $hasil[$result->tahun] = $hasil[$result->tahun] ?? [];
    $hasil[$result->tahun][] = [
        'tahun' => $result->tahun,
        'kategori_paket' => $result->jenis_pengadaan,
        'jml' => $result->jml,
    ];
}
    Login or Signup to reply.
  2. 0

    Here’s an adjusted code:

        ->join('pengadaan', 'dokumen.id_jenis_pengadaan', '=', 'pengadaan.id')
    ->select(
        DB::raw('COUNT(DISTINCT nama_paket) AS jml'),
        DB::raw('jenis_pengadaan as jenis_pengadaan'),
        DB::raw('tahun as tahun')
    )
    ->groupBy('jenis_pengadaan', 'tahun')
    ->orderBy('tahun')
    ->get();

$hasil = [];
foreach ($results as $result) {
    $kategori = $result->jenis_pengadaan;
    $jml = $result->jml;
    $hasil[] = [
        'tahun' => $result->tahun,
        'kategori_paket' => $kategori,
        'jml' => $jml,
    ];
}

dd($hasil)
    Login or Signup to reply.
  3. 0

    You can simply do this inside your foreach

    if (!isset($hasil[$tahun])) {
    $hasil[$tahun] = [];
}

$hasil[$tahun][] = [
    'kategori_paket' => $kategori,
    'jml' => $jml,
];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search