[odb-users] Example of Pagination Required.

[Boris Kolpackov]

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