Convert CTE to normal query

Question

I am trying to convert CTE to a normal query but the result is not same. This is my new query

        SELECT 
        COALESCE(CTE1.kode_barang, CTE2.kode_barang) AS kode_barang,
        COALESCE(CTE1.nama, CTE2.nama) AS nama,
        CTE1.sisa_JUNI,
        CTE1.masuk_JULI,
        CTE1.total,
        CTE2.keluar_JULI,
        CTE2.sisa_JULI
    FROM
        (
            SELECT
                COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
                COALESCE(s1.nama, s2.nama) AS nama,
                COALESCE(s1.sisa, 0) AS sisa_JUNI,
                COALESCE(s2.total_jumlah, 0) AS masuk_JULI,
                COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
            FROM
                (
                    SELECT
                        s2.max_tanggal,
                        s2.kode_barang,
                        s1.nama,
                        s2.sisa
                    FROM
                        simas_barang s1
                        LEFT JOIN (
                            SELECT
                                sm1.kode_barang,
                                sm1.tanggal AS max_tanggal,
                                sm1.created_at,
                                sm1.sisa
                            FROM
                                simas_mutasi sm1
                                JOIN (
                                    SELECT
                                        kode_barang,
                                        MAX(tanggal) AS max_tanggal,
                                        created_at
                                    FROM
                                        simas_mutasi
                                    WHERE
                                        tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                                    GROUP BY
                                        kode_barang
                                ) sm2 ON sm2.kode_barang = sm1.kode_barang
                                    AND sm2.max_tanggal = sm1.tanggal
                            GROUP BY
                                sm1.kode_barang,
                                sm1.created_at,
                                sm1.sisa
                            ORDER BY
                                sm1.kode_barang,
                                sm1.created_at DESC
                        ) s2 ON s2.kode_barang = s1.id
                    WHERE
                        s1.jenis = 'ATK DAN SEJENISNYA'
                    GROUP BY
                        s2.kode_barang
                ) s1
            LEFT JOIN (
                SELECT
                    sm.tanggal,
                    sm.kode_barang,
                    sb.nama,
                    SUM(sm.jumlah) AS total_jumlah
                FROM
                    simas_mutasi sm
                    JOIN simas_barang sb ON sb.id = sm.kode_barang
                WHERE
                    sb.jenis = 'ATK DAN SEJENISNYA'
                    AND sm.kegiatan != 'mengeluarkan stok'
                    AND sm.kegiatan != 'mengubah barang'
                    AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
                GROUP BY
                    sb.nama
            ) s2 ON s1.nama = s2.nama
        ) CTE1
    LEFT JOIN (
        SELECT
            COALESCE(sm.kode_barang, s1.id) AS kode_barang,
            COALESCE(sb.nama, s1.nama) AS nama,
            NULL AS sisa_JUNI,
            SUM(sm.jumlah) AS keluar_JULI,
            s2.sisa AS sisa_JULI
        FROM
            simas_mutasi sm
            LEFT JOIN simas_barang sb ON sb.id = sm.kode_barang
            LEFT JOIN (
                SELECT
                    sm1.kode_barang,
                    sm1.tanggal AS max_tanggal,
                    sm1.created_at,
                    sm1.sisa
                FROM
                    simas_mutasi sm1
                    JOIN (
                        SELECT
                            kode_barang,
                            MAX(tanggal) AS max_tanggal,
                            created_at
                        FROM
                            simas_mutasi
                        WHERE
                            tanggal BETWEEN '2022-07-01' AND '2022-07-31'
                        GROUP BY
                            kode_barang
                    ) sm2 ON sm2.kode_barang = sm1.kode_barang
                        AND sm2.max_tanggal = sm1.tanggal
                GROUP BY
                    sm1.kode_barang,
                    sm1.created_at,
                    sm1.sisa
                ORDER BY
                    sm1.kode_barang,
                    sm1.created_at DESC
            ) s2 ON s2.kode_barang = sb.id
        RIGHT JOIN simas_barang s1 ON s1.id = s2.kode_barang
        WHERE
            COALESCE(sb.jenis, s1.jenis) = 'ATK DAN SEJENISNYA'
        GROUP BY
            COALESCE(sb.nama, s1.nama)
        HAVING
            keluar_JULI IS NOT NULL
            AND sisa_JULI IS NOT NULL
    ) CTE2 ON CTE1.kode_barang = CTE2.kode_barang
    ORDER BY
        nama;

This is my old query with CTE.

WITH CTE1 AS (
    SELECT
        COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
        COALESCE(s1.nama, s2.nama) AS nama,
        COALESCE(s1.sisa, 0) AS sisa_JUNI,
        COALESCE(s2.total_jumlah, 0) AS masuk_JULI,
        COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
    FROM
        (
            SELECT
                s2.max_tanggal,
                s2.kode_barang,
                s1.nama,
                s2.sisa
            FROM
                simas_barang s1
                LEFT JOIN (
                    SELECT
                        sm1.kode_barang,
                        sm1.tanggal AS max_tanggal,
                        sm1.created_at,
                        sm1.sisa
                    FROM
                        simas_mutasi sm1
                        JOIN (
                            SELECT
                                kode_barang,
                                MAX(tanggal) AS max_tanggal,
                                created_at
                            FROM
                                simas_mutasi
                            WHERE
                                tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                            GROUP BY
                                kode_barang
                        ) sm2 ON sm2.kode_barang = sm1.kode_barang
                            AND sm2.max_tanggal = sm1.tanggal
                    GROUP BY
                        sm1.kode_barang,
                        sm1.created_at,
                        sm1.sisa
                    ORDER BY
                        sm1.kode_barang,
                        sm1.created_at DESC
                ) s2 ON s2.kode_barang = s1.id
            WHERE
                s1.jenis = 'ATK DAN SEJENISNYA'
            GROUP BY
                s2.kode_barang
        ) s1
    LEFT JOIN (
        SELECT
            sm.tanggal,
            sm.kode_barang,
            sb.nama,
            SUM(sm.jumlah) AS total_jumlah
        FROM
            simas_mutasi sm
            JOIN simas_barang sb ON sb.id = sm.kode_barang
        WHERE
            sb.jenis = 'ATK DAN SEJENISNYA'
            AND sm.kegiatan != 'mengeluarkan stok'
            AND sm.kegiatan != 'mengubah barang'
            AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
        GROUP BY
            sb.nama
    ) s2 ON s1.nama = s2.nama

UNION

SELECT
    COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
    COALESCE(s1.nama, s2.nama) AS nama,
    COALESCE(s1.sisa, 0) AS sisa,
    COALESCE(s2.total_jumlah, 0) AS total_jumlah,
    COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
FROM
    (
        SELECT
            s2.max_tanggal,
            s2.kode_barang,
            s1.nama,
            s2.sisa
        FROM
            simas_barang s1
            RIGHT JOIN (
                SELECT
                    sm1.kode_barang,
                    sm1.tanggal AS max_tanggal,
                    sm1.created_at,
                    sm1.sisa
                FROM
                    simas_mutasi sm1
                    JOIN (
                        SELECT
                            kode_barang,
                            MAX(tanggal) AS max_tanggal,
                            created_at
                        FROM
                            simas_mutasi
                        WHERE
                            tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                        GROUP BY
                            kode_barang
                    ) sm2 ON sm2.kode_barang = sm1.kode_barang
                        AND sm2.max_tanggal = sm1.tanggal
                GROUP BY
                    sm1.kode_barang,
                    sm1.created_at,
                    sm1.sisa
                ORDER BY
                    sm1.kode_barang,
                    sm1.created_at DESC
            ) s2 ON s2.kode_barang = s1.id
        WHERE
            s1.jenis = 'ATK DAN SEJENISNYA'
        GROUP BY
            s2.kode_barang
    ) s1
RIGHT JOIN (
    SELECT
        sm.tanggal,
        sm.kode_barang,
        sb.nama,
        SUM(sm.jumlah) AS total_jumlah,
        sm.kegiatan
    FROM
        simas_mutasi sm
        JOIN simas_barang sb ON sb.id = sm.kode_barang
    WHERE
        sb.jenis = 'ATK DAN SEJENISNYA'
        AND sm.kegiatan != 'mengeluarkan stok'
        AND sm.kegiatan != 'mengubah barang'
        AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
    GROUP BY
        sb.nama
) s2 ON s1.nama = s2.nama
WHERE
    s1.nama IS NULL
ORDER BY
    nama
), CTE2 AS (
    SELECT
        COALESCE(sm.kode_barang, s1.id) AS kode_barang,
        COALESCE(sb.nama, s1.nama) AS nama,
        NULL AS sisa_JUNI,
        SUM(sm.jumlah) AS keluar_JULI,
        s2.sisa AS sisa_JULI
    FROM
        simas_mutasi sm
        LEFT JOIN simas_barang sb ON sb.id = sm.kode_barang
        LEFT JOIN (
            SELECT
                sm1.kode_barang,
                sm1.tanggal AS max_tanggal,
                sm1.created_at,
                sm1.sisa
            FROM
                simas_mutasi sm1
                JOIN (
                    SELECT
                        kode_barang,
                        MAX(tanggal) AS max_tanggal,
                        created_at
                    FROM
                        simas_mutasi
                    WHERE
                        tanggal BETWEEN '2022-07-01' AND '2022-07-31'
                    GROUP BY
                        kode_barang
                ) sm2 ON sm2.kode_barang = sm1.kode_barang
                    AND sm2.max_tanggal = sm1.tanggal
            GROUP BY
                sm1.kode_barang,
                sm1.created_at,
                sm1.sisa
            ORDER BY
                sm1.kode_barang,
                sm1.created_at DESC
        ) s2 ON s2.kode_barang = sb.id
    RIGHT JOIN simas_barang s1 ON s1.id = s2.kode_barang
    WHERE
        COALESCE(sb.jenis, s1.jenis) = 'ATK DAN SEJENISNYA'
    GROUP BY
        COALESCE(sb.nama, s1.nama)
    HAVING
        keluar_JULI IS NOT NULL
        AND sisa_JULI IS NOT NULL
    ORDER BY
        COALESCE(sb.nama, s1.nama)
)
SELECT
    COALESCE(CTE1.kode_barang, CTE2.kode_barang) AS kode_barang,
    COALESCE(CTE1.nama, CTE2.nama) AS nama,
    CTE1.sisa_JUNI,
    CTE1.masuk_JULI,
    CTE2.keluar_JULI,
    CTE2.sisa_JULI,
    CTE1.total
FROM
    CTE1
LEFT JOIN CTE2 ON CTE1.kode_barang = CTE2.kode_barang
UNION
SELECT
    COALESCE(CTE1.kode_barang, CTE2.kode_barang) AS kode_barang,
    COALESCE(CTE1.nama, CTE2.nama) AS nama,
    CTE1.sisa_JUNI,
    CTE1.masuk_JULI,
    CTE2.keluar_JULI,
    CTE2.sisa_JULI,
    CTE1.total
FROM
    CTE2
LEFT JOIN CTE1 ON CTE1.kode_barang = CTE2.kode_barang
ORDER BY
    2;

I am new with CTE. I am trying to find a converter CTE to a normal Query or Query to laravel but I couldn't find it. What's wrong with my new query? How can I solve this problem. Please help me to fix this query.

...why do you want to get-rid of a CTE? CTEs make queries easier to read, and certain kinds of queries are impossible without CTEs. — Dai, Jul 06 '23 at 08:13
@Dai It's not supported with raw laravel. It's kinda hard for me if I have to change query to eloquent because I am a beginner in laravel. — fathimah, Jul 06 '23 at 08:17
@fathimah If that's your concern, Laravel supports running raw queries out of the box. [How to execute raw queries with Laravel 5.1?](https://stackoverflow.com/a/33049575/7376590) — steven7mwesigwa, Jul 06 '23 at 08:22
What makes you say Laravel doesn't support using CTEs in raw-queries? — Dai, Jul 06 '23 at 08:23
```WITH cte AS ( {code} ) SELECT .. FROM .. JOIN cte ..``` == ```SELECT .. FROM .. JOIN ( {code} ) AS cte ..``` — Akina, Jul 06 '23 at 09:17
@Dai It gives error `Illuminate\Database\QueryException SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CTE1 AS ( SELECT COALESCE(s1.kode_barang, s2.kode_ba' at line 1 (....` — fathimah, Jul 07 '23 at 02:12
@Dai mysql in laravel 8 is not supported with CTE because CTE is supported in MySQL 8.0+ [CTE](https://www.mysqltutorial.org/mysql-cte/#:~:text=MySQL%20introduced%20the%20common%20table,the%20statements%20in%20this%20tutorial.) [Laravel 8.x](https://laravel.com/docs/8.x/database) — fathimah, Jul 07 '23 at 03:00
@fathimah What's your current MySQL and Laravel framework versions? In addition, why don't you use raw queries with `DB::select(...)`? — steven7mwesigwa, Jul 07 '23 at 05:15

Convert CTE to normal query

0 Answers0