[Catalyst] Re: Pager and Cache

[demerphq]

2008/5/20 Aristotle Pagaltzis <pagaltzis at gmx.de>:
> * 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.

Ah ok. Right. I was thinking only of exploiting unique indexes. But
sure, what you say makes sense for non-unique indexes.

>> 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

I meant 'refetch' there.

>> page...
>
> Ah, cool! That trick never occurred to me.

Heh so we are even. :-)

yves


-- 
perl -Mre=debug -e "/just|another|perl|hacker/"