Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi, I'm new in Laravel but I'm very interesting with Laravel. I would like to create a table through query builder. I have tables 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
Could anyone show the way..? Thanks in advance.
Something like
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
oh.. thank you @tray2 you are my savior.. I spend lot of my time to find this.
hi @tray2 I have try this code to mysql and work fine, but I dont know to write on Laravel query builder, would you like to show me how..? please.
You could use DB:Select.
$result = DB::select(<the query here>);
Oh great. Thanks again.
Please or to participate in this conversation.