Repeated queries on the same instance of Selection
Notice: This thread is very old.
- DavidKrmela
- Member | 5
In version 2.1.0 I discovered the bad behavior after repeated queries on the same instance of Nette\Database\Selection with cached columns from previous request.
First request:
$selection = $context->table('test')->limit(3); // $context is instance of Nette\Database\Context
foreach ($selection as $row) {
$row->id;
}
Queries:
SELECT `id` FROM `test` LIMIT 3
// ok: returns rows [1, 2, 3]
Second request:
$selection = $context->table('test')->limit(3);
foreach ($selection as $row) {
$row->id;
$row->name; // we added $row->name
}
foreach ($selection->limit(3, 3) as $row) { // new query on the same selection
$row->id;
}
Queries:
SELECT `id` FROM `test` LIMIT 3
// ok: returns rows [1, 2, 3]
SELECT * FROM `test` WHERE (`test`.`id` IN (1, 2, 3))
// ok: returns refreshed rows [1, 2, 3]
SELECT * FROM `test` WHERE (`test`.`id` IN (1, 2, 3)) LIMIT 3 OFFSET 3
// fail: nothing returns
// it should be: 'SELECT * FROM `test` LIMIT 3 OFFSET 3' and returns [4, 5, 6]
- DavidKrmela
- Member | 5
Here it is: nette-2.1.0-database-bug.zip
You only need to import sql and run /www/bug
Problem is that Selection when refreshing rows (because missing columns) adds condition ‘WHERE primary_key IN(…keys)’ and this condition there remains for next queries. On next request it's ok because is no need to refresh rows.