Laravel – DB::TABLE SQL – how to get join data in one queries

I Have 2 tables first client_group with data like below

second table client

in Controller I have code like below:

$client = DB::table('client_group')
                ->where('client_group.user_id','=',$user_id)
                ->join('client','client_group.client_id','=','client.id')
                ->select('client_group.*',
                 'client.client_email',
                )->get();
                
return view('client.group', ['client'=>$client]);

From this query i have results like below:

IlluminateSupportCollection {#1278 ▼
#items: array:2 [▼
0 => {#1188 ▼
  +"id": 1
  +"groupname": "testowa grupa"
  +"user_id": 2
  +"client_id": "4,5,6"
  +"created_at": "2021-02-08 13:47:03"
  +"updated_at": "0000-00-00 00:00:00"
  +"client_email": "test1@wp.pl"
}
1 => {#1123 ▼
  +"id": 9
  +"groupname": "test2"
  +"user_id": 2
  +"client_id": "8,14,22"
  +"created_at": "2021-01-04 15:19:33"
  +"updated_at": null
  +"client_email": "test3@wp.pl"
}
]
}

client_id is always in one column (“client_id”: “8,14,22”) because is added like this. Now is my question and issues, how to change view and query to get insted of one email all clients emails? based on client_id, below current view. At the moment I have only one email first from client_id lists

<td>{{ $row->groupname }}</td>
<td>{{ count(explode(',',$row->client_id)) }}</td>
<td>{{ $row->client_email }}</td>

Answer

First of all , use FIND_IN_SET to join table with comma separated values. Something like this-

DB::table('client_group')
->where('client_group.user_id','=',$user_id)                
->join('client',DB::raw("FIND_IN_SET(client.id,  
  client_group.client_id)"),">",DB::raw("'0'"))
->select('client_group.*', DB::raw("GROUP_CONCAT(client.client_email) 
  as client_emails"))
->get();

And in the blade file you can get client emails like this- {{ $row->client_emails }}

Advertisement

Answer