Re: Web + Slicing/Paging datas

On Tue, Apr 28, 2009 at 2:21 PM, Jasen Betts <jasen@xnet.co.nz> wrote:
 > On 2009-04-23, durumdara <durumdara@gmail.com> wrote:
 >> Hi!
 >>
 >> In a mod_py application I wanna write a wrapper that handle all PSQL
 >> data view with paging/slicing.
 >>
 >> For example:
 >> I have 1.500 records. I wanna show only N (f. ex: 15) records in the
 >> view, other records are accessable with a pager (links):
 >
 > For small numbers of records you can use
 >
 >
 > SELECT ...
 > ... LIMIT how_many OFFSET where_to_start
 >
 >
 > If there are a large number of results this may be inefficient.
 >
 >
 > --
 > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 > To make changes to your subscription:
 > http://www.postgresql.org/mailpref/pgsql-general
 >

Perhaps another way would be to make use of some unique column(s).
Choose suitable column(s) which you have a unique constraint, construct
your query and include an "ORDER BY" clause on the unique column(s)
either ascending or descending (choose one and stick with it). In this
same query include a filter (the WHERE clause) on the unique column(s)
and use a greater than or less than filter on a value (this value will
be the value of the unique column of the last record that was returned
in the previous call to this query), for the first run of this query
supply a value either smaller than the unique column could possibly have
in your data, for example 0 for a field where values start at 1.
Each time you run your query, store the value of the chosen column of
the last record. Now use this value in the WHERE clause in the next
call. And so on.
I hope you get the idea.


May be an example:

Select id,field2,field3
from sometable
WHERE id>[dynamic_id_value]
ORDER BY id ASC
LIMIT [desired_number_of_records_per_page]
;

You will need to substitute the [dynamic_id_value] with the value of the
"id" field of the last returned record.

Allan.