John Davidson

Message:

i'm using raw query on my laravel function, and i wanna make it into laravel query builder, but i really have no idea how to do it, i've read laravel documentary about advanced join clause or subquery joins, but still cant figuring it out how to convert it

raw query :

$buku = DB::select(

        DB::raw('

        SELECT buku.*, kategory, tag

        FROM buku

        LEFT JOIN kategori_buku ON buku.id_kategori = kategori_buku.id

        LEFT JOIN detail_buku_tag ON buku.id = detail_buku_tag.id_buku

        LEFT JOIN (SELECT tag_buku.id, GROUP_CONCAT(tag) AS tag FROM tag_buku) AS tag_buku ON tag_buku.id = detail_buku_tag.id_tag

        GROUP BY buku.id')

    );

---

My Answer:
Laravel query builder equivalent:

php
$buku = DB::table('buku')
->select('buku.*', 'kategori_buku.kategory', 'tag_buku.tag')
->leftJoin('kategori_buku', 'buku.id_kategori', '=', 'kategori_buku.id')
->leftJoin('detail_buku_tag', 'buku.id', '=', 'detail_buku_tag.id_buku')
->leftJoin(DB::raw('(SELECT tag_buku.id, GROUP_CONCAT(tag) AS tag FROM tag_buku) AS tag_buku'), 'tag_buku.id', '=', 'detail_buku_tag.id_tag')
->groupBy('buku.id')
->get();


This Laravel query builder code should give you the same result as your raw query. It uses the `table`, `select`, `leftJoin`, `raw`, `groupBy`, and `get` methods to build the query.