Codeigniter 4 query builder join 2 or more tables without duplicate result

Question

I have done up a query builder using join with the following setup:

My Tables

Table users

user_id | username | email          
1       | userA    | [email protected]
2       | userB    | [email protected]
                                    
                                    

Table teams
team_id | game_id | leader_user_id
5       | 1       | 1
6       | 1       | 1
7       | 2       | 1
8       | 2       | 1   

Table games
game_id | game_name
1       | gameA
2       | gameB
3       | gameC
 

Table add_game

game_id | user_id | ign  | acc_id
1       | 1       | ignA | accA
2       | 1       | ignB | accB
3       | 1       | ignC | accC

This is my current code :

Controller

public function profile()
    {
        $data = [];
        
            $db = db_connect();
            $model = new ProfileModel($db);
            $data['profile'] = $model->getProfile();
            
            echo view('templates/header', $data);
            echo view('account/profile', $data);
            echo view('templates/footer', $data);
        
        }
    }

Model:

return $this->db->table('users')
                        ->join('add_game', 'add_game.user_id = users.user_id')
                        ->join('teams', 'teams.leader_user_id = users.user_id')
                        ->join('games', 'games.game_id = add_game.game_id')
                        ->where('users.user_id', $user_id)
                        //->groupBy('users.user_id')
                        //->distinct('users.user_id')
                        //->select(("GROUP_CONCAT(game_id, ign, acc_id) AS userdata"))
                        ->get()
                        ->getResultArray();

View

<?php

    $my_id = 0;
    foreach($profile as $row){

        if($my_id != $row['user_id']){
?>
            <div><?=$row['username']?></div> <!--data from table user-->
            <div><?=$row['game_name']?></div> <!--data from table add_game-->
            <div><?=$row['ign']?></div>
            <div><?=$row['acc_id']?></div>
            <div><?=$row['team_id']?></div>

<?php
        } else {

?>
            <div><?=$row['game_name']?></div>
            <div><?=$row['ign']?></div> <!--only data from table add_game-->
            <div><?=$row['acc_id']?></div>
            <div><?=$row['team_id']?></div>

<?php
        }

        $my_id = $row['user_id'];

    }
?>

Right now I am getting many wierd duplicated data:

userA
gameA
ignA
accA
5

gameB
ignB
accB
5

gameC
ignC
accC
5

gameA
ignA
accA
6

gameB
ignB
accB
6

gameC
ignC
accC
6

gameA
ignA
accA
7

gameB
ignB
accB
7

gameC
ignC
accC
7

gameA
ignA
accA
8

gamneB
ignB
accB
8

gameC
ignC
accC
8

I want the result display to show like this :

1. Display the username once
2. Display all the game that have added to the add_game table once
3. Display all the ign according to the game added once
4. Display all the team that have added to the teams table once

I have totally no clue how am I going to display my results without getting duplicates. Also, what or how do I need to do if I have a few more tables after that?

Answer 1

If you perform the correct joins, you'll get the output you expect:

following your description, you start with selecting from table users, then join add_game table on it, next you join game table on add_game and last you join table teams again on add_game.

the query looks like:

SELECT *
from users t1
join add_game t2
on t2.user_id = t1.user_id
join games t3
on t3.game_id=t2.game_id
join teams t4
on t4.game_id=t2.game_id
where t1.user_id=1

see it in action in this sqlfiddle

the outcome is:

user_id username email game_id user_id ign acc_id game_id game_name team_id game_id leader_user_id
1   userA   [email protected]     1   1   ignA    accA    1   gameA   5   1   1
1   userA   [email protected]     1   1   ignA    accA    1   gameA   6   1   1
1   userA   [email protected]     2   1   ignB    accB    2   gameB   7   2   1
1   userA   [email protected]     2   1   ignB    accB    2   gameB   8   2   1

this query can be built with Codeigniter:

$user_id=1;
$this->db->table('users t1')
->join('add_game t2', 't2.user_id = t1.user_id')
->join('games t3', 't3.game_id = t2.game_id')
->join('teams t4', 't4.game_id= t2.game_id')
->where('t1.user_id', $user_id)

P.S.: I'm using table aliases t1, t2, etc.., which comes in handy, if table names become quite long/descriptive. It makes not only for better visibility but also means less type-work.

here is a good explanation how JOIN works: A Visual Explanation of SQL Joins