Pagination - EASY you say: just use LIMIT, OFFSET syntax. But what happens if I want a paginated list of Authors with a list of Books by each Author next to it?
SELECT author_name,book_title FROM author INNER JOIN book USING(author_id) LIMIT 10;
Now imagine that you have 5 Authors in your database each with 20 books. That query will return 10 rows, all with the same Author in them.
So how do you paginate such a query, and hence any joined query? It's a matter of using 3 separate queries (2 if the RDBMS you are using supports LIMIT,OFFSET syntax in subselects).
SELECT count(author_id) AS total FROM author
INNER JOIN book USING(author_id);
id_list = SELECT author_id FROM author LIMIT 10;
SELECT author_name,book_title FROM author INNER JOIN
book USING(author_id) WHERE author_id IN(id_list)
If your RDBMS supports LIMIT,OFFSET syntax in subselects, then steps 2 and 3 can be done with:
SELECT author_name,book_title FROM author
INNER JOIN book USING(author_id) WHERE author_id
IN SELECT author_id FROM author LIMIT 10;
You can then use whatever programming language you like to go to a page number like this (example in PHP):
$num_pages = floor($count/$num_per_page);
if($count%$num_per_page)
$num_pages++;
if($num>$num_pages)
throw new Exception('Page: '.$num.' does not exist!');
$start = (($num-1)*$num_per_page);
Then use $start as the OFFSET and $num_per_page as the LIMIT Happy paginating!

Post new comment