[odb-users] Example of Pagination Required.

[Kanwar Manish]

Dear Boris

Sorry for lack of information - we were planning for PostgreSQL for the start.

Thanks for the solution provided.

Yes, you are right - it would be a bad idea to have a common solution
to all given the complexity of implementation in the respective DBs.

I guess it boils down to joining the below example to some flow
changes in the application - to have something completely at Data
Layer to support such stuff when data would be changing too fast -
would not be a very good idea. User looking at the data will only get
confused. I guess we will try and make changes to the data flow itself
first to accommodate some aspects of the problem.

Kind Regards
Kanwar Manish

On Fri, Jan 13, 2012 at 9:28 PM, Boris Kolpackov
<boris at codesynthesis.com> wrote:
> Hi Kanwar,
>
> Kanwar Manish <kanwarmanish at gmail.com> writes:
>
>> Let us say I am getting all the products currently available [5000
>> scanners] in stock under a category. All the 5000 products have a
>> unique ID [something like GUID]. But the user would be browsing the
>> results in a browser - 20 rows at a time. So we don't want to have the
>> entire 5000 result set coming back to the client/browser - only 20
>> rows. [5000 is an example count, it can be 10 times this count easily
>> in our scenario]
>>
>> Also we will show user a page number for every consecutive set of 20
>> rows. So by clicking on 1st page link he sees records 1-20 and by
>> clicking on 10th page link - he sees records 181-200.
>
> I tried to think of a database-independent solution to this and while
> it is probably possible to come up with something working (but quite
> complex) for certain limited cases (such as when you have a column
> without duplicate values that is used to order the result), in the
> end the consensus seems to be the best solution is to use the database-
> specific mechanisms. See this discussion for example:
>
> http://stackoverflow.com/questions/5486815/generic-pagination-system
>
> You didn't say which database you plan to use. Here is sample code
> (based on the 'hello' example) that shows how to implement pagination
> that will work for MySQL, PostgreSQL, and SQLite:
>
>    typedef odb::query<person> query;
>    typedef odb::result<person> result;
>
>    // For better performance you may wish to move the begin()/end()
>    // calls inside the for-loop. Note, however, that if the table
>    // is modified while paging, some records may get missed.
>    //
>    transaction t (db->begin ());
>
>    size_t page_size (5);
>    size_t offset;
>
>    // Note that for most databases to return consistent result the
>    // query has to have the ORDER BY clause.
>    //
>    query q ((query::age > 10) + "ORDER BY" + query::age +
>             + "LIMIT"
>             + query::_val (page_size) + "OFFSET"
>             + query::_ref (offset));
>
>    for (size_t p (0);; ++p)
>    {
>      offset = p * page_size;
>
>      result r (db->query<person> (q));
>
>      size_t n (0);
>      for (result::iterator i (r.begin ()); i != r.end (); ++i, ++n)
>      {
>        cout << i->first () << " " << i->last () << " " << i->age () << endl;
>      }
>
>      if (n < page_size)
>        break;
>
>      cout << endl; // Page break.
>    }
>
>    t.commit ();
>
> For Oracle, Microsoft SQL Server, and IBM DB/2 things are considerably
> more complicated. The solution seems to be to use the row number pseudo-
> column together with nested queries (if you need ORDER BY). This also
> means that you won't be able to use pagination directly with object
> queries in ODB. Instead, you will need to use views (see Chapter 9,
> "Views" in the ODB manual).
>
> Boris