List users from database with CodeIgniter 4

Question

I'm new in CodeIgniter 4 and i need to show a list of users with user details from multiple tables: users, users_details, user_contact_details, company, company_positions, departments. In table users_details i have foreign keys for company, company positions and departments.

I really need your support.

Here is my code.

Thank you!

Model User_Model.php

namespace App\Models;
use CodeIgniter\Model;

class User_Model extends Model {
    
    
    public function get_user()
    {
        return $this->db->table('users')
        ->join('users_details','users_details.user_id_fk = users.user_id')     
        ->join('user_contact_details','user_contact_details.user_id_fk = users.user_id')      
        ->get()->getResultArray();
    }
    
        public function get_user_company_details()
    {
        return $this->db->table('users_details')
        ->join('company','company.company_id = users_details.company_id_fk')          
        ->join('company_positions','company_positions.position_id = users_details.position_id_fk')
        ->join('departments','departments.department_id = users_details.department_id_fk')        
        ->get()->getResultArray();
    }
   
}

Controller Users.php

namespace App\Controllers;
use CodeIgniter\Controller;
use App\Models\User_Model;

class Users extends BaseController
{     
    protected $User_Model;
        
        public function __construct()
        {
            $this->User_Model = new User_Model();
        }
        
        public function list_users()
        {
            $data=[
                'user' =>$this->User_Model->get_user(),
            ];
            
         foreach ($data['user'] as $type) {
            
            $type = [
                'company' => $this->User_Model->get_user_company_details(),
                ];
     }
            echo view('users/list_users', $data);
        }

}

View list_users.php

<table id="useful_links" class="table table-striped projects" role="grid" aria-describedby="example2_info">
   <thead>
      <tr>
         <td>Id</td>
         <td>Name</td>
         <td>Phone</td>
         <td>E-mail</td>
         <td>Vacation days</td>   
         <td>Position</td>  
         <td>Active</td>
         <td>Deleted</td>
         <td>Options</td>
      </tr>
   </thead>
   <?php
      $n=1;
      foreach ($user as $row)
      {?>
   <tr>
      <td><?php echo $n;?>
      <td><div class="user-block">
              <span class="username" style="margin-top:5px;"><?php echo $row['surname']." ".$row['firstname'];?> </span></div></td>
      <td><?php echo $row['user_phone'];?></td>
      <td><?php echo $row['user_email'];?></td>
      <td><?php echo $row['yearly_vacation_days'];?></td>
      <td>
          <?php  foreach ($company as $row)
      {
              echo $row['position_name'];
      }?>
      </td>
      <td><?php echo $row['user_active'];?></td>
      <td><?php echo $row['user_deleted'];?></td>
      <td>
         <a href="<?php echo site_url('/useful_links/editUseful_link/'.$row['link_id']);?>" class="btn btn-primary btn-sm" href="#"><i class="fas fa-folder"></i> View</a>&nbsp;
         <a href="<?php echo site_url('/useful_links/editUseful_link/'.$row['link_id']);?>" class="btn btn-info btn-sm"><i class="fas fa-pencil-alt"></i> Edit</a>&nbsp;
         <a href="<?php echo site_url('/useful_links/delete/'.$row['link_id']);?>" class="btn btn-danger btn-sm"><i class="fas fa-trash"></i> Delete</a>
      </td>
      <?php $n++;   }
         ?>
</table>

Answer 1

You'll need to expand on what your problem actually is to get a true "answer" but I have two cents to give you that's a little too long for a comment.

I recently had a project using CI4 that put me in the same situation: most of my business logic needed information that spanned across multiple tables, but using a single Model to span multiple tables doesn't fit well into CI's design of one Model per table.

I still created all of the 'inner' CI-extending Model classes that each represent a single table, but I also created my own 'outer' Model-type class which does not extend CI's Model class.

This 'outer' Model has no direct access to any database, but instead acts as a "container" for both the business logic and 'inner' Models; the 'inner' Models meanwhile maintain their boundaries to a single table, not accessing any data outside of its associated table (e.g. no joins).

When the Controller goes to a Model to perform business logic, the Model it goes to is actually my 'outer' Model rather than one of the CI-extending 'inner' models. Then from within that 'outer' model, I can construct/interact with whichever 'inner' models I need, depending on which tables I need to perform the requested business logic, to fetch all the data I need to fulfill the request.

So instead of View <> Controller <> Model, the flow looks more like View <> Controller <> Outer Model <> Inner Models

I ended up liking this approach the best because it a) keeps the code more readable overall, b) still contains all business logic to the Model and c) still allows you to take full advantage of a CI-extended Model's functionality (built-in getters/setters, Entity classes, etc.).

Again this isn't a specific answer to your question but possibly a valid way for you to think about the problem as a whole.

Answer 2

In you case what you need to use then is the model callbacks.

First your model should have more information in order to use this feature like this:

<?php namespace App\Models;
use CodeIgniter\Model;

class User_Model extends Model {
    
    protected $table      = 'users';
    protected $primaryKey = 'id';
    protected $returnType     = 'array';
    protected $allowedFields = ['name', 'email']; // You should expand this to the exact fields you're updating and editing
    protected $afterFind = ['userCompanyDetails'];

    /**
        Now since this is a afterFind callback there's a find things 
        you'll have to consider, since your $user will vary depending 
        on how you call this model

        $user->find()

        id = the primary key of the row being searched for.
        data = The resulting row of data, or null if no result found.

        $user->findAll()

        data = the resulting rows of data, or null if no result found.
        limit = the number of rows to find.
        offset = the number of rows to skip during the search.

        $user->first()

        data = the resulting row found during the search, or null if none found.
    */
    protected function userCompanyDetails($user) {
        $db      = \Config\Database::connect();
        $builder = $db->table('users_details');
        
        if(isset($user['id']) {
           $user['data']['user_details'] = $builder->join('users_details','users_details.user_id_fk = users.user_id')     
    ->join('user_contact_details','user_contact_details.user_id_fk = users.user_id')
    ->where('user_details.user_id_fk', $user['data']['id'])     
    ->get()->getResultArray();
        } else {
        
             foreach($user['data'] as $key => $u){
                 $user['data'][$key]['user_details'] = $builder->join('users_details','users_details.user_id_fk = users.user_id')     
                 ->join('user_contact_details','user_contact_details.user_id_fk = users.user_id')
                 ->where('user_details.user_id_fk', $user['data']['id'])     
                 ->get()->getResultArray();
             }
        }

        return $user;
    }
   
}

You can have as many events you want and they can do their own queries expanding on the user information.

As for you controller I would also change a few things, first you shouldn't really be using the construct magic method in CI4 controllers. Instead you should use the init method as described in your base controller.

namespace App\Controllers;
use CodeIgniter\Controller;
use App\Models\User_Model;

class Users extends BaseController
{     
        protected $User_Model;
        
        public function initController(\CodeIgniter\HTTP\RequestInterface $request, \CodeIgniter\HTTP\ResponseInterface $response, \Psr\Log\LoggerInterface $logger)
        {
            $this->UserModel = new User_Model();
        }
        
        public function list_users()
        {
            $data['user'] = $this->User_Model->findAll();
            return view('users/list_users', $data);
        }

}

This way you're just using the findAll function from your CodeIgniter Model and adding that to your data.

For more information about the model events you can read here: https://codeigniter.com/user_guide/models/model.html#model-events

Or if you prefer some guidelines on video you can check them here: https://www.youtube.com/watch?v=_GBBAAC_dcs&ab_channel=TILthings