Convert mysql Query to DB:: raw query

Question

does anyone know, how to convert this query to raw querybuilder:

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

i had tried this,

$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()

but i got error

Call to a member function get() on array

score 0 · Answer 1 · answered Oct 20 '17 at 01:53

0

you use the ->get() on the "colletc" function

remove the ->get() it should work

i suggest you to use the model relations and you dont need to make querys like you do

answered Oct 20 '17 at 01:53

Peter Haberkorn

259
1
9

thanks @peter, i have replace ->get() with all(), thats work perfect. – YK. toe Oct 21 '17 at 02:18

score 0 · Answer 2 · answered Oct 20 '17 at 02:20

Try this

$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,
]);

This query is build using Named Bindings. You can read more about it here

In the query, word :param1 will be replaced with value of $bulan

:param2 replaced with $tahun...

:param1 in the query needs to match key of the array provided as second argument to DB::select() (which in this case is "param1" => $bulan,)

One thing you need to know is if you want to use same $bulan variable again, you need to add new one with :param3 that will get the value of "bulan3" => $bulan,

thank you verru much, i got error, .. and i give DB::Raw( befor db::select). and work fine — YK. toe, Oct 21 '17 at 02:20
be careful when you use `DB::raw()` especially if you are passing variables (example `$bulan`) inside the query. That could leave you open to [sql injection](https://stackoverflow.com/questions/601300/what-is-sql-injection). That's why I provided you with solution that uses **Named Bindings** — ljubadr, Oct 21 '17 at 03:50

Convert mysql Query to DB:: raw query

2 Answers2