将 mysql 查询转换为 DB::原始查询

Question

有谁知道，如何将此查询转换为原始查询生成器：

SELECT
    A.ID_KANWIL,
    COUNT(A.ID_CABANG) AS CABANG,
    SUM(JUMLAH_KARYAWAN) AS JMLKARYAWAN,
    COALESCE( B.JML_PESERTA, 0 ) AS JMLPESERTA 
FROM ORGANISASI A
    LEFT JOIN (
        SELECT 
            ID_KANWIL,
            SUM(JUMLAH_PESERTA) AS JML_PESERTA 
        FROM CAKUPAN 
        WHERE 
            BULAN=6 
            AND TAHUN=2017 
            AND ID_PROGRAM=1 
        GROUP BY 
            ID_KANWIL,
            ID_PROGRAM
    ) B ON A.ID_KANWIL=B.ID_KANWIL
WHERE 
    A.BULAN=6 
    AND A.TAHUN=2017 
GROUP BY 
    A.ID_KANWIL, 
    A.BULAN, 
    A.TAHUN

我试过这个，

$data=colletc(DB::select( DB::raw("SELECT
    A.ID_KANWIL,
    COUNT(A.ID_CABANG) AS CABANG,
    SUM(JUMLAH_KARYAWAN) AS JMLKARYAWAN,
    COALESCE( B.JML_PESERTA, 0 ) AS JMLPESERTA 
FROM ORGANISASI A
    LEFT JOIN (
        SELECT 
            ID_KANWIL,
            SUM(JUMLAH_PESERTA) AS JML_PESERTA 
        FROM CAKUPAN 
        WHERE 
            BULAN=6 
            AND TAHUN=2017 
            AND ID_PROGRAM=1 
        GROUP BY 
            ID_KANWIL,
            ID_PROGRAM
    ) B ON A.ID_KANWIL=B.ID_KANWIL
WHERE 
    A.BULAN=6 
    AND A.TAHUN=2017 
GROUP BY 
    A.ID_KANWIL, 
    A.BULAN, 
    A.TAHUN") ))->get() 

但是我有错误

Call to a member function get() on array

Answer 1

你在"colletc"函数上使用->get()

删除 ->get（） 它应该可以工作

我建议您使用模型关系，并且不需要像您那样进行查询

Answer 2

试试这个

$data = DB::select(
  "
  SELECT A.ID_KANWIL,
        COUNT(A.ID_CABANG) AS CABANG,
        SUM(JUMLAH_KARYAWAN) AS JMLKARYAWAN,
        COALESCE(B.JML_PESERTA, 0) AS JMLPESERTA
  FROM ORGANISASI A
  LEFT JOIN
    (
     SELECT ID_KANWIL,
            SUM(JUMLAH_PESERTA) AS JML_PESERTA
     FROM CAKUPAN
     WHERE BULAN = :param1
       AND TAHUN = :param2
       AND ID_PROGRAM = 1
     GROUP BY ID_KANWIL,
              ID_PROGRAM
    ) B
    ON A.ID_KANWIL = B.ID_KANWIL
  WHERE A.BULAN = :param3
    AND A.TAHUN = :param4
  GROUP BY A.ID_KANWIL, A.BULAN, A.TAHUN
  ",
  [
    "param1" => $bulan,
    "param2" => $tahun,
    "param3" => $bulan,
    "param4" => $tahun,
]);

此查询是使用命名绑定生成的。您可以在此处阅读更多相关信息

在查询中，单词 :param1 将替换为值 $bulan

:param2替换为$tahun...

查询中的:param1需要将作为第二个参数提供的数组的键与DB::select()匹配（在本例中为 "param1" => $bulan, ）

您需要知道的一件事是，如果您想再次使用相同的$bulan变量，您需要添加新的变量，:param3该变量将获得 "bulan3" => $bulan, 的值