CodeIgniter4: Bug: in-model pager ignores the model event (with where clause, run via callback), reporting incorrect paging information

PHP Version

8.1

CodeIgniter4 Version

4.4.4

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

cli-server (PHP built-in webserver)

Database

SQLite3

What happened?

I have a custom event in a model that applies where clause to every search implemented thus:

protected $beforeFind = ['restrictToUser'];

    protected function restrictToUser(array $data)
    {
        // in my case the user_id comes from session, hardcode here:
        $this->where($this->table . '.user_id', 3);

        return $data;
    }

When I use it with an in-model pager, the find results apply this event, but the pager does not, resulting in wrong page count and wrong total record number.

Steps to Reproduce

Start with empty project. Rename env to .env, change these lines in it to the values:

app.baseURL = 'http://localhost:8080/'
database.default.database = ci4
database.default.DBDriver = SQLite3

Add a cli route in app/Config/Routes.php:

$routes->cli('home', 'Home::index');

Add migration for a table:

php spark make:migration CreateTestTable

Replace up() and down() methods with:

public function up()
    {
        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 10,
                'auto_increment' => true,
                'null'           => false,
            ],
            'user_id' => [
                'type'       => 'INT',
                'constraint' => 10,
                'null'       => false,
            ],
            'data' => [
                'type'       => 'VARCHAR',
                'constraint' => 255,
                'null'       => false,
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'deleted_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->createTable('test');
    }

    public function down()
    {
        $this->forge->dropTable('test');
    }

make a seeder:

php spark make:seeder test --suffix

Replace the seeder method run() with this:

public function run()
    {
        $builder = $this->db->table('test');
        $data = [];
        
        $i = 1;
        while ($i < 100) {

            $user_id = rand(2, 4);
            $data = [
                'user_id'        => $user_id,
                'data'           => 'Some text by user ' . $user_id . ', entry ' . $i,
                'created_at'     => '2024-01-09 12:51:33',
                'updated_at'     => '2024-01-09 12:51:33',
            ];

            $builder->insert($data);

            $i++;
        }
    }

Create model:

php spark make:model TestModel

in model, update the $beforeFind callback thus:

protected $beforeFind = ['restrictToUser'];

and add event method to model:

    protected function restrictToUser(array $data)
    {
        // in my case the user_id comes from session, hardcode here:
        $this->where($this->table . '.user_id', 3);

        return $data;
    }

Add test code to the Home controller, replace the index() method with:

    public function index()
    {
        $model = model(\App\Models\TestModel::class);
        echo PHP_EOL;

        echo 'Total number of entries in table (should be 99): ' . $model->countAllResults(false);
        echo PHP_EOL . PHP_EOL;

        $list = $model->findAll();
        //$list = $model->where('user_id', 3)->findAll();

        echo 'Number with user_id = 3, using findAll(), event restrictToUser applied (should be ~1/3 of 99): ' . count($list);
        echo PHP_EOL . PHP_EOL;

        echo 'Rerun find with paginate() (squeze all results in one page):' ;
        $list = $model->paginate(101);
        $pager = $model->pager;
        echo '... done.' . PHP_EOL . PHP_EOL;

        echo 'Real number of results, that honours event restrictToUser (should be ~1/3 of 99): ' . count($list);
        echo PHP_EOL . PHP_EOL;

        echo 'pager-reported number, that ignores event restrictToUser  (should be ~1/3 of 99, but is 99): ' . $pager->getTotal();
        echo PHP_EOL . PHP_EOL;

        echo 'Rerun find with  paginate() (10 per page):' ;
        $list = $model->paginate(10);
        $pager = $model->pager;
        echo '... done.' . PHP_EOL . PHP_EOL;

        echo 'Pager-reported number, that ignores event restrictToUser (should be ~1/3 of 99, but is 99): ' . $pager->getTotal();
        echo PHP_EOL . PHP_EOL;

        echo 'Pager-reported page number, that ignores event restrictToUser (should be ~3, but is 10) : ' . $pager->getPageCount();
        echo PHP_EOL . PHP_EOL;

    }

Run the test :

dg@tvenkinys:~/Programavimas/MOK/ci4bugDemo$ php spark migrate

CodeIgniter v4.4.4 Command Line Tool - Server Time: 2024-01-12 08:44:50 UTC+00:00

Running all new migrations...
        Running: (App) 2024-01-10-214032_App\Database\Migrations\CreateTestTable
Migrations complete.
dg@tvenkinys:~/Programavimas/MOK/ci4bugDemo$ php spark db:seed TestSeeder

CodeIgniter v4.4.4 Command Line Tool - Server Time: 2024-01-12 08:45:12 UTC+00:00

Seeded: App\Database\Seeds\TestSeeder

dg@tvenkinys:~/Programavimas/MOK/ci4bugDemo$ php public/index.php home

Total number of entries in table (should be 99): 99

Number with user_id = 3, using findAll(), event restrictToUser applied (should be ~1/3 of 99): 28

Rerun find with paginate() (squeze all results in one page):... done.

Real number of results, that honours event restrictToUser (should be ~1/3 of 99): 28

pager-reported number, that ignores event restrictToUser  (should be ~1/3 of 99, but is 99): 99

Rerun find with  paginate() (10 per page):... done.

Pager-reported number, that ignores event restrictToUser (should be ~1/3 of 99, but is 99): 99

Pager-reported page number, that ignores event restrictToUser (should be ~3, but is 10) : 10

Expected Output

I expect the in-model pager report the same number of entries as returned by the model paginate() method, applying the in-model specified event correctly.

Anything else?

No response

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 18 (15 by maintainers)

Most upvoted comments

@mavelo-llc Please check this PR: #9617

I don’t know how to fix this issue or if we should not fix it.

Workaround is to override paginate() something like this?

I see; I was looking at the code yesterday and also could not envisage a way to improve it (but thought maybe the more experienced people might). The suggested workaround would work I guess while I only have that single callback; either that or just add the needed where clause manually before the call to pagination (as I do now; it does generate a double WHERE but it works).

I am leaving the issue open, but I am ok with it being closed as WONTFIX if an efficient solution cannot be found. Thanks for investigating, @kenjis!

My theory of this error after looking in the code of BaseModel.php:

The $beforeFind callbacks are fired in the findAll() method, so, at the very last element of the chain of building sql command.

The method paginate() uses the the findAll() method internally, but only calls it AFTER in initiates the Pager library and adds all the data to it. So, the Pager is initiated before the callback runs within the findAll(), on line 1239:

$this->pager = $pager->store($group, $page, $perPage, $this->countAllResults(false), $segment);

How does that sound?

Try countAllResults(false).

Tried; it changes nothing. Updated the test code above. And thanks for code colouring 😃

You reset the query with countAllResults().

echo 'total number of entries in table: ' . $model->countAllResults();

Try countAllResults(false).