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 :(
CodePudding user response:
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