How to generate from SQL query Laravel Builder?

Question

Laravel

I have a SQL query, how can I rewrite it in Laravel Query Builder?

Request:

SELECT cat.ID AS ID, cat.Platform AS Platform, cat.Create_Date AS Create_Date, cat.Name AS Name, cat.Status AS Status, cat.Image AS Image, count(*) AS item_count FROM items AS it INNER JOIN category AS cat ON it.Item LIKE CONCAT('%"category":"', cat.ID, '"%') WHERE cat.Platform = '5f771d465f4191.76733056' GROUP BY cat.ID

Answer 1

DB::select(`cat.ID as ID`,`cat.Platform as Platform`,`cat.Create_Date as Create_Date`,`cat.Name as Name`,`cat.Status as Status`,`cat.Image as Image`)
    ->addSelect(DB::raw(`count(*) as item_count`))
    ->from(`items as it`)
    ->join(`category as cat`, function($join) {
      $join->on(`it.Item`, `LIKE`, `CONCAT('%"category":"', cat.ID, '"%')`);
      })
    ->where(`cat.Platform`, `=`, `5f771d465f4191.76733056`)
    ->groupBy(`cat.ID`)
    ->get();