Next.Js filter and pagination using MySQL with custom query

Many times we may need to develop API which can provide filtered data with pagination. But the challenging part is to how to apply different kind of filters and pagination at a time. Suppose a scenario where we want to search products on e-commerce site. Site contains data lf Laptops where user want to search laptop with 8 GB or RAM with intel processor which range between 50k – 60k where graphics card is optional

In above scenario we have to query data from data base like where condition where RAM = 8 and price <=50 and price >=60 or graphics_card = true

After applying this condition in SQL you may need to manage total count, current page no as well as records per page. We will manage all of these queries but what about some different filter condition or different product ? Then we may need to develop the API which can dynamically add column name , value and operator as per filter applied by user. Also number of records per page, total records as per condition, current page no and sort order .

First you need to go through this blog if you are new to NEXTJS

I’ve done this sample code for some limited scenarios you can add more such conditions as per your need . Link is at bottom.

I’ve created the car project where you get car brand name , model name like basic data

Query to get matching equal numeric data.

if (elementQuery.MatchMode == "NUM_EQUALS") {
          tempSQLSTR = " = ";
          stringSQL = stringSQL + elementQuery.FilterName + tempSQLSTR + " " + elementQuery.FilterValue;
          tempSQLSTR = "";
        } 

In above example we prepare first example of query where simple numeric value compared with column value. if there are multiple conditions we have to loop it and check each time which kind of filter is there.

For example if we want car name which starts with string “fo” then we have to prepare query using this code

if (elementQuery.MatchMode == "STARTSWITH") {
          tempSQLSTR = " = ";
          stringSQL = stringSQL + elementQuery.FilterName  + " LIKE '" + elementQuery.FilterValue+"%' ";
          tempSQLSTR = ""; 
        }

so like these example you can create multiple scenarios and run through loops. If there are 5 filters applied then loop will rotate through all these multiple condition and will prepare query. In final we can run that query by applying LIMIT as per record per page value. OFFSET for page number and also calculate total count of data.

Many times user will also ask for sort . So as per user request we can sort data as given input . Here is sample input JSON data

{
    "PageNo": 1,
    "RecordsPerPage": 10,
    "SortOrder": 1,
    "SortColumn": "make_id",
    "FilterApplied": true,
    "Filters": [
        {
            "FilterName": "make_name",
            "FilterValue": "or",
            "MatchMode": "CONTAINS"
        }
    ]
}

in this JSON data it explain page no , records per page, sort order and sort column . then there is array of objects which contains the information that which column need to filter out “FilterName” . “FilterValue” for value to be applied and “MatchMode” to decide which operator to be used for query

There can be multiple object like this so we can run the loop

Link for github repo for sample code

comment section is open to discuss to enhance this code