如何将此SQL查询转换为Laravel语法？

Question

在这里，我的MySQL查询(工作在phpMyAdmin中)：

    SELECT waktu_kerusakan, workcenter, COUNT(workcenter) AS jumlah_repair 
    FROM repair WHERE year(waktu_kerusakan)='2019'  GROUP BY workcenter , 
    month(waktu_kerusakan) BETWEEN 1 and 6 Order By jumlah_repair Desc

然后，我试着用以下的Laravel语法(不起作用)：

    $sql = Main::groupBy('workcenter')->select('workcenter', \DB::raw('count(*) as frekuensi'))
                ->whereYear('waktu_kerusakan', 'like',  "%".$tahun."%")
                ->OrderBy('frekuensi', 'Desc')
                ->groupBy(\DB::raw("MONTH(waktu_kerusakan)"), [1, 6])
                ->get();

请任何人帮助我将MySQL查询转换为Laravel语法。谢谢你！

Answer 1

尝尝这个。

    $repair = DB::table('repair')
    ->select('waktu_kerusakan','workcenter', DB::raw('COUNT(workcenter) AS jumlah_repair'))
->whereYear('waktu_kerusakan', $tahun)
    ->groupBy(DB::raw("MONTH(waktu_kerusakan)"), 'workcenter')
  ->whereIn(DB::raw('MONTH("waktu_kerusakan")'),[1, 6])
    ->orderBy('frekuensi', 'DESC')
    ->get()

Answer 2

请尝试以下查询：

$sql = Main::select('workcenter', \DB::raw('count(*) as frekuensi'),\DB::raw('MONTH(waktu_kerusakan) as waktu_kerusakan_month'))
            ->where('waktu_kerusakan',$tahun)
            ->whereBetween('waktu_kerusakan_month',[1, 6])
            ->groupBy('workcenter','waktu_kerusakan_month')
            ->OrderBy('jumlah_repair', 'Desc')
            ->get();