How to create laravel multiple joins with count and display all data

Question

In the Below Code i was using multiple table joins and i have multiple rows with the same id from the table clients_topup table while i need to count multiple rows from the table and display all data from the other two tables

in the below code i was getting only count values how to make it out

foreach ($clients as $client) {
            $clientsLevelOne = DB::table('clients')
            ->leftjoin('clients_payment_type','clients_payment_type.user_id', '=', 'clients.id')
            ->leftjoin('clients_topup', 'clients_topup.user_id', '=', 'clients.id')
            ->select(DB::raw("SUM(topup_amount) as count"))
            ->where('clients.under_reference','=',$client->reference_id)->get();        

            $users[] = $clientsLevelOne;
          }

score 0 · Answer 1 · answered Dec 13 '19 at 09:42

0

i think you to do like this:

  $client_id_list =  [];
  foreach ($clients as $client) {
      $client_id_list[] => $client->id,
  }

 $clientsLevelOne = DB::table('clients')
            ->select(DB::raw("SUM({table_name}.topup_amount) as count"))
            ->leftjoin('clients_payment_type','clients_payment_type.user_id', '=', 'clients.id')
            ->leftjoin('clients_topup', 'clients_topup.user_id', '=', 'clients.id')
            ->where('clients.under_reference','=',$client->reference_id)
            ->whereIn('client_id', $client_id_list)
            ->groupBy('client_id')
            ->get();

answered Dec 13 '19 at 09:42

LuongPs

99
6

not working it shows only the count values not all the other columns from the table – praveenkumar N Dec 13 '19 at 11:18
you have to add the name of the column you want to get in select(). – LuongPs Dec 16 '19 at 02:06
i want get all the fields from clients table and clients_payment_type table can you add some examples @ LuongPs – praveenkumar N Dec 19 '19 at 10:06

How to create laravel multiple joins with count and display all data

1 Answers1