spadefoot/kohana-orm-leap — Issue #17: OFFSET and LIMIT error - again
Author: ekarlso
Created: 2012-01-27 08:51:38+00:00
State: open

Hi, when doing $select->page($offset, $limit) it should be like this i think:

if (($this->data['limit'] > 0) && ($this->data['offset'] > 0)) {
$max_row_to_fetch = $this->data['offset'] + ($this->data['limit'] - 1);
$min_row_to_fetch = $this->data['offset'];
$sql = "SELECT * FROM (SELECT \"t0\".*, ROWNUM AS \"rn\" FROM ({$sql}) \"t0\" WHERE ROWNUM <= {$max_row_to_fetch}) WHERE \"rn\" >= {$min_row_to_fetch}";
}

cause basically if i do $s->page(0, 20) it should do:
string(178) "SELECT * FROM (SELECT * FROM "DB_COPY" ORDER BY "ID" DESC) WHERE ROWNUM <= 20;
Total Pages: 37
Total Rows: 734
Requested Page: 1
Per Page: 20
From Row: 0
"

It should be doing 0 > 20 since then in "effect" max row will be up to and row 20

Whilst the the next page is $s->page(21, 20) this should do something like:
string(238) "SELECT * FROM (SELECT "t0".*, ROWNUM AS "rn" FROM (SELECT * FROM "DB_COPY" ORDER BY "ID" DESC) "t0" WHERE ROWNUM <= 40) WHERE "rn" >= 21;
Total Pages: 37
Total Rows: 734
Requested Page: 2
Per Page: 20
From Row: 21
"

It should be taking the next 20 rows so you actually get in effect 20 rows in the result and not 19 like with the current code.