Pagination - Express

ビュー

views/users/index.ejs

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <link rel="stylesheet" href="/stylesheets/style.css">
  <title>Document</title>
</head>
<body>
  <div id="header">
    <h1>User Index</h1>
  </div>
  <div id="content">
    <table>
        <tr>
          <th>ID</th>
          <th>Name</th>
          <th>Age</th>
        </tr>
        <% for(let i in users){ %>
          <% let obj = users[i]; %>
          <tr>
            <td><%= obj.id %></td>
            <td><%= obj.name %></td>
            <td><%= obj.age %></td>
          </tr>
        <% } %>
    </table>
    <ul id="pagination" class="clearfix">
      <% if(page > 1){ %>
      <li><a class="paging_item" href="/users/1" title="最初のページへ"><<</a></li>
      <% } else { %>
      <li><span class="paging_item"><<</span></li>
      <% } %>

      <% if(page > 1){ %>
      <li><a class="paging_item" href="/users/<%= prev %>" title="前のページへ"><</a></li>
      <% } else { %>
      <li><span class="paging_item"><</span></li>
      <% } %>

      <% if(page > 1){ %>
      <li><a class="paging_item" href="/users/1">1</a></li>
      <% } else { %>
      <li><span class="paging_item current">1</span></li>
      <% } %>

      <% if(start > pageRange){ %>
      <li><span>...</span></li>
      <% } %>

      <% for(let i in nums) { %>
        <% if(nums[i] == page){ %>
        <li><span class="paging_item current"><%= nums[i] %></span></li>
        <% } else { %>
        <li><a class="paging_item" href="/users/<%= nums[i] %>" class="num"><%= nums[i] %></a></li>
        <% } %>
      <% } %>

      <% if ((totalPage - 1) > end){ %>
      <li><span>...</span></li>
      <% } %>


      <% if(page < totalPage){ %>
      <li><a class="paging_item" href="/users/<%= totalPage %>"><%= totalPage %></a></li>
      <% } else if(totalPage == 1 || totalPage == 0) { %>
      <% } else { %>
      <li><span class="paging_item current"><%= totalPage %></span></li>
      <% } %>

      <% if(page < totalPage){ %>
      <li><a class="paging_item" href="/users/<%= next %>">></a></li>
      <% } else { %>
      <li><span class="paging_item">></span></li>
      <% } %>

      <% if(page < totalPage){ %>
      <li><a class="paging_item" href="/users/<%= totalPage %>">>></a></li>
      <% } else { %>
      <li><span class="paging_item">>></span></li>
      <% } %>
    </ul>
  </div>
</body>
</html>

public/stylesheets/style.css

body {
  padding: 50px;
  font: "Lucida Grande", Helvetica, Arial, sans-serif;
}
a {
  color: #00B7FF;
}
.clearfix:after {
  content: "";
  clear: both;
  display: block;
}
#pagination li {
  list-style: none;
  float: left;
  margin-right: 20px;
  line-height: 24px;
}
#pagination li a {
  text-decoration: none;
}

自作ライブラリ

lib/pagination.js

module.exports = class Pagination {
  constructor(total){
    this.view = 10; // 1ページに表示するレコード数
    this.page_range = 2; // リンクを前後何ページ分表示するかの設定
    this.count = total; // レコード数
    this.total_page = Math.ceil(this.count / this.view); // ページ総数
    this.current_page; // 現在のページ
    this.sql_start = (this.current_page - 1) * this.view;
    this.nums = []; // ページ番号格納用
  };

  notParam(){
    this.current_page = 1;
    this.sql_start = (this.current_page - 1) * this.view;

    let prev = Math.max(this.current_page - 1, 1);
    let next = Math.min(this.current_page + 1, this.total_page);

    let start = Math.max(this.current_page - this.page_range, 2);
    let end = Math.min(this.current_page + this.page_range, this.total_page - 1); // ページ番号終点
    for (let i = start; i <= end; i++) { this.nums.push(i) }

    let data = {
      view: this.view,
      sql_start: this.sql_start,
      total_count: this.count,
      prev: prev,
      next: next,
      start: start,
      end: end,
      nums: this.nums,
      page: this.current_page,
      pageRange: this.page_range,
      totalPage: this.total_page
    }
    
    return data;
  }

  param(param){
    this.current_page = param;
    this.sql_start = (this.current_page - 1) * this.view;

    let prev = Math.max(this.current_page - 1, 1);
    let next = Math.min(Number(this.current_page) + 1, this.total_page);

    let start = Math.max(this.current_page - this.page_range, 2);
    let end = Math.min(Number(this.current_page) + Number(this.page_range), this.total_page - 1); // ページ番号終点

    for (let i = start; i <= end; i++) { this.nums.push(i) }

    let data = {
      view: this.view,
      sql_start: this.sql_start,
      total_count: this.count,
      prev: prev,
      next: next,
      start: start,
      end: end,
      nums: this.nums,
      page: this.current_page,
      pageRange: this.page_range,
      totalPage: this.total_page
    }
    
    return data;
  }
}

ルーティング

app.js

var usersRouter = require('./routes/users');
app.use('/users', usersRouter);

routes/users.js

var express = require('express');
var router = express.Router();
const Users = require('../models/User');
const Pagination = require('../lib/pagination');

router.get('/', function(req, res, next) {
  new Promise((resolve)=>{
    Users.countUser().then((total) => {
      let pagination = new Pagination(total);
      let data = pagination.notParam();
      
      Users.limitUser(data["sql_start"], data["view"]).then((results) => {
        data['users'] = results;
        data['msg'] = "";
        res.render('users/index', data);
      })
    })
  })
});

router.get('/:page', function(req, res, next) {
  new Promise((resolve)=>{
    Users.countUser().then((total) => {
      let pagination = new Pagination(total);
      let data = pagination.param(req.params.page);
      
      Users.limitUser(data["sql_start"], data["view"]).then((results) => {
        data['users'] = results;
        data['msg'] = "";
        res.render('users/index', data);
      })
    })
  })
});

module.exports = router;

モデル

models/User.js

const mysql = require('mysql2/promise');
const pool = require('../db');

module.exports = {
  countUser: () => {
    return new Promise ((resolve, reject) => {
      (async () => {
        try {
          const [results, fields] = await pool.query('SELECT COUNT(*) FROM users');
          resolve(results[0]["COUNT(*)"]);
        } catch (err) {
          console.log(err);
        }
      })();
    });
  },
  limitUser: (sql_start, view) => {
    return new Promise ((resolve, reject) => {
      (async () => {
        try {
          const [results, fields] = await pool.query('SELECT * FROM users  LIMIT ?, ?', [sql_start, view]);
          resolve(results);
        } catch (err) {
          console.log(err);
        }
      })();
    });
  }
}

データベース設定

db.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'users',
  password: 'password',
});

module.exports = pool;

サンプルデータ投入用プログラム

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'users',
  password: 'password',
});

(async () => {
  try {
    for(let i = 0; i < 200; i++){
      await pool.query('INSERT INTO users (name, age) VALUES (?, ?);', ["taro" + i, i] );
    }
  } catch (err) {
    console.log(err);
  }
  pool.end();
})();