As I have to render large data and when I am doing client-side pagination then it's taking time and I know Server side pagination is best for
- Large data set
- Faster initial page load
But I don't have any idea how to do Server Side pagination using NODE.JS EJS and MYSQL here is my Routes and.EJS
Routes
app.get('/',function(req,res,next){
req.getConnection(function(error, conn) {
let sql = `SELECT *FROM studiestable
WHERE ReceivingDate >= ( CURDATE() - INTERVAL 35 DAY )
ORDER BY Bckup DESC,
ReceivingDate DESC`;
conn.query(sql,function(err,rows,fields){
if (err) {
req.flash('error', err)
res.render('patient/dashboard', {
title: 'Dashboard',
data: ''
})
} else {
res.render('patient/dashboard', {
title: 'Dashboard',
data: rows
})
}
})
})
})
EJS
<table id="pattTab" class="table small">
<tr style="background-color: rgb(122, 135, 160);color: white">
<th>ID</th>
<th>Patient Name</th>
<th>Age</th>
<th>Modality</th>
<th>Images</th>
</tr>
<% if (data) { %>
<% data.forEach(function(Patient){ %>
<tr>
<td><%= Patient.PatientID %></td>
<td><%= Patient.PatientName %></td>
<td><%= Patient.Age %></td>
<td><%= Patient.Modality %></td>
<td><%= Patient.Images %></td>
</tr>
<% }) %>
<% } %>
</table>
As I have to render large data and when I am doing client-side pagination then it's taking time and I know Server side pagination is best for
- Large data set
- Faster initial page load
But I don't have any idea how to do Server Side pagination using NODE.JS EJS and MYSQL here is my Routes and.EJS
Routes
app.get('/',function(req,res,next){
req.getConnection(function(error, conn) {
let sql = `SELECT *FROM studiestable
WHERE ReceivingDate >= ( CURDATE() - INTERVAL 35 DAY )
ORDER BY Bckup DESC,
ReceivingDate DESC`;
conn.query(sql,function(err,rows,fields){
if (err) {
req.flash('error', err)
res.render('patient/dashboard', {
title: 'Dashboard',
data: ''
})
} else {
res.render('patient/dashboard', {
title: 'Dashboard',
data: rows
})
}
})
})
})
EJS
<table id="pattTab" class="table small">
<tr style="background-color: rgb(122, 135, 160);color: white">
<th>ID</th>
<th>Patient Name</th>
<th>Age</th>
<th>Modality</th>
<th>Images</th>
</tr>
<% if (data) { %>
<% data.forEach(function(Patient){ %>
<tr>
<td><%= Patient.PatientID %></td>
<td><%= Patient.PatientName %></td>
<td><%= Patient.Age %></td>
<td><%= Patient.Modality %></td>
<td><%= Patient.Images %></td>
</tr>
<% }) %>
<% } %>
</table>
- Datatable is best option for pagination at server side. May be this link useful to you datatables/examples/data_sources/server_side – Sadikhasan Commented Nov 30, 2018 at 10:43
- hi sadil but i have to do that without data tables plugin that doesn't perform well for continuous changing data – ArunPratap Commented Nov 30, 2018 at 10:46
-
I dont get
continuous changing datameans – Sadikhasan Commented Nov 30, 2018 at 10:48 - sadil i have multiple events after rendering my table and for that i have to render my table again and again after events and when you have large data then with that data table doesn't perform well see here i had used datatable before stackoverflow./questions/50405711/… – ArunPratap Commented Nov 30, 2018 at 10:51
-
Have you considered using SQL's
OFFSETandLIMIT? – ionizer Commented Dec 1, 2018 at 19:24
2 Answers
Reset to default 3Pagination in NodeJS, EJS, and MongoDB, using Mongoose ODM
If you are trying to do Pagination the do it the following way. Your Node.JS file
const ITEMS_PER_PAGE = 4;
exports.getIndex = (req, res, next) => {
// start constants
const page = +req.query.page || 1; // pagination
let totalItems; // pagination
// end constants
Hotel.find()
.countDocuments()
.then(numberOfProducts => {
totalItems = numberOfProducts;
return Hotel.find()
.skip((page-1) * ITEMS_PER_PAGE)
.limit(ITEMS_PER_PAGE);
}).then(hotels => {
res.render('pages/hotel', {
hotels: hotels,
currentPage: page,
hasNextPage: (ITEMS_PER_PAGE * page) < totalItems,
hasPreviousPage: page > 1,
nextPage: page + 1,
previousPage: page - 1,
lastPage: Math.ceil(totalItems / ITEMS_PER_PAGE)
});
}).catch(err => {
console.log(err);
});
}
And then write your ejs in the following manner:
<body>
<div class="jumbotron">
<div class="container">
<h1 class="display-4">Hotels</h1>
</div>
</div>
<div class="container">
<%if (hotels.length > 0) {%>
<div class="row">
<%for (let hotel of hotels) {%>
<div class="col-sm-4">
<div class="card mt-4" style="width: 18rem;">
<div class="card-body">
<h5 class="card-title mt-2"><%=hotel.title%></h5>
<p class="card-text"><%=hotel.description%></p>
</div>
</div>
</div>
<%}%>
<div class="container mt-4">
<nav aria-label="Page navigation example">
<ul class="pagination justify-content-center">
<% if (hasPreviousPage) { %>
<li class="page-item">
<a class="page-link" href="?page=1">First</a>
</li>
<li class="page-item">
<a class="page-link" href="?page=<%= previousPage %>"><%= previousPage %></a>
</li>
<% } %>
<li class="page-item active">
<a class="page-link" href="?page=<%= currentPage %>"><%= currentPage %></a>
</li>
<% if (hasNextPage) { %>
<li class="page-item">
<a class="page-link" href="?page=<%= nextPage %>"><%= nextPage %></a>
</li>
<li class="page-item">
<a class="page-link" href="?page=<%= lastPage %>">Last</a>
</li>
<% } %>
</ul>
</nav>
</div>
</div>
<%} else {%>
<div class="text-center">
<h5>No products found.</h5>
<div class="mt-4">
<a href="/" class="btn btn-outline-primary">Go Home</a>
</div>
</div>
<%}%>
</div>
</body>
Have used bootstrap for CSS.
This is how you can do it. It will fetch a few items from database and paginate the UI.
You might want to try using SQL's LIMIT and OFFSET for pagination. So, it'll be something like this for your Node's SQL string:
`SELECT * FROM studiestable
WHERE ReceivingDate >= ( CURDATE() - INTERVAL 35 DAY )
ORDER BY Bckup DESC,
ReceivingDate DESC`
LIMIT 30 OFFSET ${req.query.page*30};`
I know, passing such variable unsanitized is very unsecure, but just to give a rough idea.
So what happens in this one is, you receive a query string called page (example url: api.example./?page=0). We'll assume our page numbers start from 0. So, when page is equal to 0, it returns first 30 rows (0 offset). Then when page is equal to 1 (30 offset), it returns the next 30 rows. And as page increases, it returns the next 30 set of data depending on your page.