Paginate with oracle

[mklappen]

Hi, I have noticed that the cakephp paginate limit parameter does not seem to be working when using this plugin. Has anyone else experienced this?

have fairly basic paginate setup in my index action as show below. When I have my app pointed to mysql it paginates fine showing 5 entries on each page and Paginator returns this in view "Page 1 of 20, showing 5 records out of 99"

However when I switch over to my oracle DB pagination is lost... that is all table entries are shown on first page. The Paginator has correct number of pages, however all entries are shown on page 1 and view renders Paginator as below: "Page 1 of 7, showing 31 records out of 31"

Are there any special parameters/settings in order to get the limit option passed correctly?

  public function index()
  {
    $this->paginate = [
            'limit' => 5,
        'order'     => ['Articles.created' => 'desc']
    ];
    $this->set('articles', $this->paginate($this->Articles));   
    $this->set('_serialize', ['articles']);
  }

[mklappen]

In looking at the SQL being passed to oracle from debug there's no "rownum" (or "offset") call which I imagine would be needed in place of the limit parameter that's passed from cakephp paginate.

[snelg]

Oracle did not have built-in offset/limit functions until very recently (server version 12.1). In order to do pagination-like queries, you had to do some funky stuff with rownum. http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html I'm mostly supporting old Oracle systems, so I did not include the offset/limit stuff at all. I'll look into adding offset/limit support, but with a flag so the driver does not try to use those functions if your Oracle server does not support them.

[mklappen]

Thanks for the reply, yeah I'm working on 11g right now so it would have to use rownum.

I can take a look at replacing limit passed from cakephp to rownum... just not entirely sure where the SQL is getting created or converted in the driver you created.

[snelg]

Make sure you look at that link I included above. The really important part is that when Oracle is running the query, ROWNUM is created before the ORDER BY clause. So you can't add a "limit" clause by simply sticking "where rownum < 10" or something like that into the query.

[mklappen]

Thx, will read through it and try to take a look but not sure I follow the flow from the driver. I see where the SQL from public function prepare($query) function in Oracle.php but getting a bit lost from there on how I would update the query strings passed there.

[snelg]

Bad news: it's ugly Good news: it's done :) New release (1.0.13) includes LIMIT and OFFSET support, even for 11g and earlier (i.e. using the ugly wrapper stuff) Let me know if it works for you.

[snelg]

Oh, dang. Works when you're manually adding "->limit(20)" or whatever to a Query, but doesn't work with pagination yet. The pagination auto-generated "select count(*)" query breaks. I'll figure out how to fix it.

[snelg]

Release 1.0.14 works correctly with pagination.

[mklappen]

wow, great news... nice surprise to see this morning. Just when I was going to start tracing down if I could do myself. Will install and give it a go today and let you know how it works for me. Thanks!

[mklappen]

Just updated the driver and pagination working as expected. Thanks so much for quick update on this, appreciated!