Pagination（改良） - Express

データベース情報

データベース名
mydb

データベース mydb の作成

CREATE DATABASE mydb;

users テーブルの作成

CREATE TABLE users (
  id INT AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT NULL,
  PRIMARY KEY (id)
);

以下のスクリプトをターミナルで実行し、サンプルデータの登録をします。

script.js

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

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

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

ビュー

app/views/myapp/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 of users){ %>
          <tr>
            <td><%= i.id %></td>
            <td><%= i.name %></td>
            <td><%= i.age %></td>
          </tr>
        <% } %>
    </table>

    <ul id="pagination" class="clearfix">
      <li><a class="paging_item" href="/myapp/1">First</a></li>

      <% if(page > 1){ %>
      <li><a class="paging_item" href="/myapp/<%= prev %>" title="前のページへ"><</a></li>
      <% } else { %>
      <li><span class="paging_item"><</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="/myapp/<%= nums[i] %>" class="num"><%= nums[i] %></a></li>
        <% } %>
      <% } %>

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

      <% if(totalPage != 0) { %>
        <li><a class="paging_item" href="/myapp/<%= totalPage %>">LAST</a></li>
      <% } %>
      
    </ul>
  </div>
</body>
</html>

app/public/stylesheets/style.css

body {
  padding: 50px;
  font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;
}

a {
  color: #00B7FF;
}
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;
}

自作ライブラリ

app/lib/pagination.js

module.exports = class Pagination {
  constructor(total, page_num, current_page, link_num){
    this.view = Number(page_num); // 1ページに表示するレコード数
    this.count = Number(total); // レコード数
    this.total_page = Math.ceil(this.count / this.view); // ページ総数
    this.current_page = Number(current_page); // 現在のページ
    this.sql_start = (this.current_page - 1) * this.view;
    this.nums = []; // ページ番号格納用
    this.link = link_num; // 表示するリンクの数
  };

  paginate(){
    let prev = Math.max(this.current_page - 1, 1);
    let next = Math.min(this.current_page + 1, this.total_page);
    let number = Math.floor(this.link / 2);

    if(this.total_page < this.link) {
      for (let i = 1; i <= this.total_page; i++) { this.nums.push(i) }
    } else {
      if(this.current_page <= this.link){
        for (let i = 1; i <= this.link; i++) { this.nums.push(i) }
      } else if(this.current_page >= this.total_page - number) {
        for (let i = this.total_page - this.link + 1; i <= this.total_page ; i++) { this.nums.push(i) }
      } else {
        if (this.link % 2 == 1) {
          for (let i = this.current_page - number; i <= this.current_page + number; i++) { this.nums.push(i) }
        } else if (this.link % 2 == 0){
          for (let i = this.current_page - number; i <= this.current_page - 1 + number; i++) { this.nums.push(i) }
        }
      }
    }

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

ルーティング

app/app.js

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var topsRouter = require('./routes/tops');
var myappRouter = require('./routes/myapp');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/tops', topsRouter);
app.use('/myapp', myappRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

app/routes/myapp.js

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

router.get('/:page', function(req, res, next) {
  Users.countMax().then((record_max) => {
    return new Promise((resolve, reject) => {
      resolve(record_max)
    })
  }).then((record_max) => {
    let pagination = new Pagination(record_max, 10, req.params.page);
    let data = pagination.paginate();
    
    Users.allUser(data["sql_start"], data["view"]).then((results) => {
      data['users'] = results;
      res.render('myapp/index', data);
    })
  })
});

module.exports = router;

let pagination = new Pagination(record_max, 10, req.params.page);

record_max: データベースのレコード総数

10: 1ページに表示するレコードの数

req.params.page: 現在のページ数

モデル

app/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);
        }
      })();
    });
  }
}

データベース設定

app/config/db.js

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

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

module.exports = pool;

myapp へのリンク

<a href="/myapp/1">myapp</a>