how to use subquery with join in the Laravel query builder

Hello Geek, In this post, I will demonstrate how to use subquery with join in the Laravel query builder. If you need to use subquery in your Laravel project, you can use the following example to learn how. In the following example, you can see that we added a subquery using DB::raw(). DB::raw() allows us to select our subtable and then compare your field in the second argument. You can then easily fire subqueries with the Laravel

$data = DB::table("items")
  ->select("items.*","items_count.price_group","items_count.quantity_group")
  ->join(DB::raw("(SELECT 
      items_count.id_item,
      GROUP_CONCAT(items_count.price) as price_group,
      GROUP_CONCAT(items_count.quantity) as quantity_group
      FROM items_count
      GROUP BY items_count.id_item
      ) as items_count"),function($join){
        $join->on("items_count.id_item","=","items.id");
  })
  ->groupBy("items.id")
  ->get();
print_r($data);

All the best nerd!