**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:**

- SELECT * from table_1 where <conditions> ORDER BY id DESC LIMIT pageSize;
**total_rows_table_1 =**SELECT count(*) from table_1 where <condition>- SELECT * from table_2 where <conditions> ORDER BY id DESC LIMIT pageSize;
**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.