Home > Software engineering >  Laravel join 3 table and select raw sum
Laravel join 3 table and select raw sum

Time:04-09

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

  • Related