Ad

Laravel Query LeftJoin Show All Categories Even In Right Table Have Not That Category

- 1 answer

I have two table following:

tb_category

id  |name      |
----------------
1   |Category A|
2   |Category B|
3   |Category C|
4   |Category D|
5   |Category E|

tb_sales
id |customer   |region       |product       |category
-------------------------------------------------------
1   James       Region A      Fruits         Category A
2   Billy       Region B      Vegetable      Category B
3   Benny       Region C      Computer       Category C
4   John        Region A      Table          Category D
5   Sarah       Region B      Fruits         Category A
6   James       Region A      Computer       Category C
7   Clara       Region B      Fruits         Category A
8   Benny       Region C      Vegetable      Category B
9   James       Region A      Vegetable      Category B
10  Billy       Region C      Fruits         Category A

I would like to create query and result a table like table below where all category count and show even in tb_sales have not category.

No  |name           |Region A   |Region B   |Region C
-----------------------------------------------------
1   Category A         1          1           1
2   Category B         1          1           1
3   Category C         1          0           1
4   Category D         1          0           0
5   Category E         0          0           0

in my controller I have write like this:

            $cat = DB::table('tb_sales')
                ->leftJoin('tb_category','tb_category.name', '=', 'tb_sales.category')
                ->select(DB::raw('category, region, count(category) as c_Cat'))
                ->groupBy('category', 'region')
                ->get();

                return view('category', ['Category'=> $cat]);

Could anyone help my this problem..? Thanks in advance.

Ad

Answer

I has solved with answer from laracast @Tray2. Thanks @Tray2.

use this query:

SELECT c.name, 
         (SELECT count(s.*) from tb_sales s WHERE category = c.name AND s.region = 'Region A') AS region_a,
         (SELECT count(s.*) from tb_sales s WHERE category = c.name AND s.region = 'Region B') AS region_b,
             (SELECT count(s.*) from tb_sales s WHERE category = c.name AND s.region = 'Region C') AS region_c
FROM tb_category c
ORDER by c.name ASC

and passing that query to controller:

$result = DB::select(<the query here>);

I hope this can help someone with similar problem.

Ad
source: stackoverflow.com
Ad