Laravel join 3 table and select raw sum

Question

I have a 3 tables in Laravel project

First table "offers"

id	client	numer_offer	id_user
1	123	211/2022	11
2.	145	212/2022	23

Second table "clients"

id	name	adres
123	Mark	211/2022
145	Ben	212/2022.

A the last table "offer_items"

id	id_offer	product	amount
1	2	bags	14.56
2	2	bags2	16.50

And have a query:

  $id_user = '11';
  $offers = Offer::join('clients', 'clients.id', '=', 'offers.client')
  ->join('offer_items','.offer_items.id_offer', '=', 'offers.id')
  ->selectRaw(' sum(offer_items.amount) as suma, clients.name, offers.*')
  ->where('offers.id_user', $id_user)
  ->groupBy('offer_items.id_offer')
  ->Orderby('offers.id_offer')
  ->get();

the query works fine if I have a record in "offer_items", but if I have no record in the table, nothing shows, and I would like everything to be displayed and amount = 0.

any idea because yesterday I was up all day :(

Answer 1

use leftJoin instead of join at joining with offer_items, to retrieve data whether has records on offer_items or not , also i added IFNULL to treat null as 0

$id_user = '11';
 $offers = Offer::join('clients', 'clients.id', '=', 'offers.client')
      ->leftJoin('offer_items','offer_items.id_offer', '=', 'offers.id')
      ->selectRaw(' sum(IFNULL(offer_items.amount,0)) as suma, clients.name, offers.*')
      ->where('offers.id_user', $id_user)
      ->groupBy('offers.id')
      ->Orderby('offers.id')
      ->get();

more details about different type of join , Mysql IFNULL