[Catalyst] Re: Pager and Cache

[Aristotle Pagaltzis]

* demerphq <demerphq at gmail.com> [2008-05-20 15:10]:
> 2008/5/20 Aristotle Pagaltzis <pagaltzis at gmx.de>:
> > * demerphq <demerphq at gmail.com> [2008-05-20 01:10]:
> >> If you want to exploit indexes in paging properly you need
> >> to involve an index in the search criteria and remember the
> >> last fetched value. IE:
> >>
> >>   select * from Foo where id >= last_id_fetched LIMIT $size
> >
> > ++
> >
> > (Note to readers: using `>=` instead of `>` here is crucial.
> > Think about why.)
> 
> Er actually you got me there. :-) I had even contemplated
> posting a corrective follow up so im curious what i'm missing.
> (Although having the last record of the previous fetch
> including in the next page is often nice from continuity
> perspective.)

Heh. Well, imagine you have 5 records with equal key values, but
only the first 2 of them fit at the end of the page you are
looking at. If you use `>`, you will never see the other 3.

Of course, the extra record will throw off your count, but that’s
easy to correct: instead of a constant `LIMIT`, add the number of
equal-key records from the end of the page (which is 1 if there
was only one such record, of course), and filter them out in code
on the next page.

> Theres another trick, use $size+1 when fetching, do not display
> the +1 record and use it as a flag to indicate that there are
> more pages available. If you get the extra record show the
> "next page link", if you dont then you have reached the end of
> the data set. And in this case the >= is important, as you will
> want to refact the +1 record as the 0 record on the next
> page...

Ah, cool! That trick never occurred to me.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>