Pagination On Multiple Table In MYSQL

Disclaimer: This is my first ever blog/post so in case someone has some feedback I am happy to take them, if someone has some correction I am happy to incorporate them, if some has comment I am happy to choose/ignore them & finally if someone has some question I am happy to reply them.

Introduction: Content of this blog/post are closely associated with technical domain. To some extent it explain a problem I faced in my work and a very simple solution to it. And I felt it could be a better starting point for me to start writing blog’s, and what the heck I will give it a try, nothing is better then trying it yourself.

Problem: Pagination (moving to next or previous or first or last page) in web-site is a simple problem when it comes to show data from a single table, but how would you perform pagination when data is coming from multiple tables?

Multiple tables add extra dimension to the problem, and in discussion below I am going to try to explain this issue in very generic terms.

Setup: Suppose you have a two tables, lets call the table_1 and table_2, and these table has a column created in following format YYYY-MM-DD HH:mm:ss (this is crucial for sorting).

Solution: Before we start lets understand what all variables we need to tackle this problem, (P will denote on which page we are)

• pageSize : Number of rows we are showing in each page
• countTable_1: Number of rows we have shown from table_1 till current page (P)
• countTable_2: Number of rows we have shown from table_2 till current page (P)
• previousCountTable_1: Number of rows we have shown from table_1 in last page (P-1)
• previousCountTable_2: Number of rows we have shown from table_2 in last page (P-1)
• isNext : if next page is called  i.e. (P+1)
• isPrevious: if previous page is called i.e. (P-1)
• isLast: if last page is called
• isFirst: if first page is called

Algorithm:

Every time when pagination will be called will get above mentioned variables and out of (isNext, isPrevious, isLast, isFirst) variables only one of them will be true which will denote which operation is called.

Example could be: We are page 2 and moving to page 3. Page one has 6 entries from table_1 and 4 entries from table_2. Page two has 5 entries each from table_1 and table_2.

pagination(pageSize=10, countTable_1=11, countTable_2=9, previousCountTable_1=6, previousCountTable_2=4, isNext=true, isPrevious=false, isFirst=false, isLast=false)

Next:

SqlQueries would be:

table_1 -> SELECT * from table_1 where <conditions> LIMIT countTable_1, countTable_1 + pageSize;

table_2 -> SELECT * from table_2 where <conditions> LIMIT countTable_2, countTable_2 + pageSize

From above 2 queries we will get next 10 rows from table_1 and table_2 which we can merge, then sort them based on created, finally pick first 10 rows from them.

Then we compute number of rows we got from table_1 and table_2 as #rows_1 and #rows_2, respectively.

previousCountTable_1 = countTable_1;

previousCountTable_2 = countTable_2;

countTable_1 = countTable_1 + #rows_1;

countTable_2 = countTable_2 + #rows_2;

 Previous:

SqlQueries would be:

table_1 -> SELECT * from table_1 where <conditions> LIMIT previousCountTable_1 – pageSize, previousCountTable_1;

table_2 -> SELECT * from table_2 where <conditions> LIMIT previousCountTable_2 – pageSize,, previousCountTable_2;

From above 2 queries we will get previous (from P -1) 10 rows from table_1 and table_2 which we can merge, then sort them based on created, finally pick last 10 rows from them.

Then we compute number of rows we got from table_1 and table_2 as #rows_1 and #rows_2, respectively.

countTable_1 = previousCountTable_1

countTable_2 = previousCountTable_2

previousCountTable_1 = previousCountTable_1 – #rows_1

previousCountTable_2 = previousCountTable_2 – #rows_2

Last:

1. SELECT * from table_1 where <conditions> ORDER BY id DESC LIMIT pageSize;
2. total_rows_table_1 = SELECT count(*) from table_1 where <condition>
3. SELECT * from table_2 where <conditions> ORDER BY id DESC LIMIT pageSize;
4. total_rows_table_2 = SELECT count(*) from table_2 where <condition>

From above queries 1 and 3 you will get last 10 rows from each tables, which can be merged and sort. Now question is which all rows to show in last page for that we need to perform a little computation.

remainder = (total_rows_table_1 + total_rows_table_2)%pageSize

if remainder = 0 then show last 10 rows from the sorted list else show last remainder number of rows from the sorted list.

countTable_1 = total_rows_table_1

countTable_2 = total_rows_table_2

previousCountTable_1 = total_rows_table_1 – #rows_1

previousCountTable_2 = total_rows_table_2 – #rows_2

First

SqlQueries would be:

table_1 -> SELECT * from table_1 where <conditions> LIMIT countTable_1, countTable_1 + pageSize;

table_2 -> SELECT * from table_2 where <conditions> LIMIT countTable_2, countTable_2 + pageSize

From above 2 queries we will get first 10 rows from table_1 and table_2 which we can merge, then sort them based on created, finally pick first 10 rows from them.

Then we compute number of rows we got from table_1 and table_2 as #rows_1 and #rows_2, respectively.

previousCountTable_1 = 0;

previousCountTable_2 = 0;

countTable_1 = #rows_1;

countTable_2 = #rows_2;

NOTE: In previous case we have subtracted pageSize from previousCountTable_#, we need to make sure that our count should not fall below zero in those cases.