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

Tags: , , ,

I Have 2 tables first client_group with data like below

enter image description here

second table client

enter image description here

in Controller I have code like below:

$client = DB::table('client_group')
return view('', ['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": ""
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": ""

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>

enter image description here


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

->select('client_group.*', DB::raw("GROUP_CONCAT(client.client_email) 
  as client_emails"))

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

Source: stackoverflow