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)
@mavelo-llc Please check this PR: #9617
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
$beforeFindcallbacks are fired in thefindAll()method, so, at the very last element of the chain of building sql command.The method
paginate()uses the thefindAll()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 thefindAll(), on line 1239:How does that sound?
Tried; it changes nothing. Updated the test code above. And thanks for code colouring 😃
You reset the query with
countAllResults().Try
countAllResults(false).