Codeigniter 4 display records using join with multiple tables

You have a few options here.. recursive SQL (slow), some hideous GROUP_CONCAT or a better proposed solution below.

PHP Code:

$teams = $this->db->table('team_list t1')
    ->join('teammember_list t2', 't2.team_id = t1.team_id')
    ->join('game_list t3', 't3.game_id = t2.game_id')
    ->join('add_game t4', 't4.game_id = t3.game_id')
    ->where('t2.user_id', $user_id)
    ->get()
    ->getResultArray();

$teamIds = array_column($teams, 'team_id');

$teamMembers = $this->db->table('teammember_list t2')
    ->whereIn('t2.team_id', $teamIds)
    ->get()
    ->getResultArray();

foreach ($teams AS &$team) {
    $team['members'] = array_filter(
        $teamMembers,
        fn($teamMember) => $teamMember['team_id'] === $team['team_id']
    );
} 


Then you can loop through your teams for the table rows, and in the member column you can loop through the $team['members']