如何在CodeIgniter 4模型中添加WHERE查询条件

Got it! Adding a WHERE condition to filter your comments in CodeIgniter 4 is super straightforward. You just need to chain the where() method to your model query before calling paginate(). Let's walk through some common scenarios, plus fix a small bug in your original code that's preventing your footer from rendering.

First, fix the footer rendering issue #

In your current code, the return view('comments', $data); line stops execution immediately—so the echo view('templates/footer'); never runs. To fix this, just echo all your views in sequence instead of returning early:

public function comments() {
    $model = new CommentsModel();
    
    // Your WHERE conditions will go here
    
    $data = [
        'comments' => $model->paginate(50),
        'pager' => $model->pager
    ];
    
    echo view('templates/header');
    echo view('templates/aside');
    echo view('comments', $data);
    echo view('templates/footer'); // Now this will render!
}

Adding WHERE conditions #

Now let's add filters based on your needs:

1. Single static condition

If you want to filter for comments from a specific user (e.g., com_user = 'john_doe'):

public function comments() {
    $model = new CommentsModel();
    
    // Add your WHERE clause
    $model->where('com_user', 'john_doe');
    
    $data = [
        'comments' => $model->paginate(50),
        'pager' => $model->pager
    ];
    
    echo view('templates/header');
    echo view('templates/aside');
    echo view('comments', $data);
    echo view('templates/footer');
}

2. Multiple conditions

Need to filter by multiple fields? Chain additional where() methods:

public function comments() {
    $model = new CommentsModel();
    
    // Filter by user AND email, and exclude empty phone numbers
    $model->where('com_user', 'john_doe')
          ->where('com_email', 'john@example.com')
          ->where('com_phone !=', '');
    
    $data = [
        'comments' => $model->paginate(50),
        'pager' => $model->pager
    ];
    
    echo view('templates/header');
    echo view('templates/aside');
    echo view('comments', $data);
    echo view('templates/footer');
}

3. Dynamic conditions (from user input/URL)

If you want to filter based on a dynamic value (like a username from the URL), use parameter binding to avoid SQL injection:

First, update your route to accept a parameter (e.g., $routes->get('comments/(:any)', 'YourController::comments/$1');), then modify your controller:

public function comments($username = null) {
    $model = new CommentsModel();
    
    if ($username) {
        // Safe parameter binding automatically handled by CodeIgniter
        $model->where('com_user', $username);
    }
    
    $data = [
        'comments' => $model->paginate(50),
        'pager' => $model->pager
    ];
    
    echo view('templates/header');
    echo view('templates/aside');
    echo view('comments', $data);
    echo view('templates/footer');
}

4. Reusable conditions with scopes (cleaner for repeated use)

If you need to use the same filter across multiple controller methods, add a scope to your model:

class CommentsModel extends Model {
    protected $table = 'comments';
    protected $allowedFields = ['com_user', 'com_email', 'com_phone', 'comment'];
    
    // Define a reusable scope
    public function scopeByUser($query, $username) {
        return $query->where('com_user', $username);
    }
}

Then use it in your controller like this:

$model->byUser('john_doe');

That's it! Pick the approach that fits your specific filtering needs.

内容的提问来源于stack exchange，提问作者Virginus Alajekwu