Node-活动申请模块

接下来我们写一个活动申请模块部分

• 数据库设计

我们需要设计两个表格来存储活动信息和用户的申请信息：

activities: 存储活动的基本信息。
applications: 存储用户的活动申请信息。

1. 创建表activities（活动信息）

CREATE TABLE activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    location VARCHAR(255) NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 创建活动接口

引入我们封装好的方法

封装好的方法在之前文章讲了，这里就不在重复了。

const express = require('express');
const router = express.Router();

const connectionPool = require('../db'); // 引入数据库连接池模块
const {addCondition,addDateRangeCondition,addPagination,convertKeysToSnakeCase} = require('../methods.js'); // 引入封装方法
const {listApi,getApi,addApi,updateApi,delApi,changeStatusApi} = require('../apimethods.js'); // 引入封装方法

查询列表

// 查询列表
router.get('/',(req, res) => {
    // 查询数据库列表
    let query = `SELECT * FROM activities`;
    // 查询数据库总数
    let sqltotal = `SELECT COUNT(*) AS total FROM activities`
     // 查询数据库并返回数据
    const { title,type, page_num, page_size } = convertKeysToSnakeCase(req.query);
    listApi(query,sqltotal,req,res,{title,type, page_num, page_size});
});

新增列表

// 新增  POST
router.post('/', (req, res) => {
  const insertSql = 'INSERT INTO activities SET ?'; // 准备 SQL 插入语句
  const { title, description} = convertKeysToSnakeCase(req.body);
  const postData = {title, description};
  addApi(insertSql,res,postData);
});

获取详情

// 获取详情 3
router.get('/:id', (req, res) => {
    const { id } = req.params;
    const values = [id];
    let querySql = 'SELECT * FROM activities WHERE id = ?';
    getApi(querySql,values,res);
});

更新接口

// 更新接口 
router.put('/', (req, res) => {
  // console.log(req,'req');
  const { title, description,id} = convertKeysToSnakeCase(req.body);
  const updatevalues = {title, description};
  let updateSql='UPDATE activities SET ? WHERE id = ?';
  updateApi(updateSql,[updatevalues, id],res);
});

删除接口

// 删除数据 DELETE请求处理程序  
router.delete('/:id', (req, res) => {
    // const {id} = req.body; // 从请求体中获取数据
    const id = req.params.id;
    const delsql = "DELETE FROM activities WHERE id = ?";
    delApi(delsql,[id],res);
});

3. applications 表（用户申请信息）

CREATE TABLE applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    applied_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE
);

4. 创建申请活动接口

引入我们封装好的方法

封装好的方法在之前文章讲了，这里就不在重复了。

const express = require('express');
const router = express.Router();

const connectionPool = require('../db'); // 引入数据库连接池模块
const {addCondition,addDateRangeCondition,addPagination,convertKeysToSnakeCase} = require('../methods.js'); // 引入封装方法
const {listApi,getApi,addApi,updateApi,delApi,changeStatusApi} = require('../apimethods.js'); // 引入封装方法

查询列表

// 查询列表
router.get('/',(req, res) => {
    // 查询数据库列表
    let query = `SELECT * FROM applications`;
    // 查询数据库总数
    let sqltotal = `SELECT COUNT(*) AS total FROM applications`
     // 查询数据库并返回数据
    const { title,type, page_num, page_size } = convertKeysToSnakeCase(req.query);
    listApi(query,sqltotal,req,res,{title,type, page_num, page_size});
});

SQL化一下从sql之中查到用户的姓名和活动的名称,也就是在查询applications表的时候，利用表中的user_id去查询users表的name和avatar，利用表中的activity_id去查询activities表的title

SQL如何在查询applications表的时候，利用表中的user_id去查询users表的name和avatar，利用表中的activity_id去查询activities表的title
通过 JOIN 将 activities 表与 users 表连接，查询用户的 name 和 phone

SELECT 
    a.id AS application_id, 
    u.name AS user_name, 
    u.avatar AS user_avatar, 
    act.title AS activity_title
FROM applications a
JOIN users u ON a.user_id = u.id
JOIN activities act ON a.activity_id = act.id;

新增

这里注意给申请一个默认的状态

// 新增  POST
router.post('/', (req, res) => {
  const insertSql = 'INSERT INTO activities SET ?'; // 准备 SQL 插入语句
  const { title, description} = convertKeysToSnakeCase(req.body);
  const postData = {title, description};
  addApi(insertSql,res,postData);
});

获取详情

// 获取详情 3
router.get('/:id', (req, res) => {
    const { id } = req.params;
    const values = [id];
    let querySql = 'SELECT * FROM activities WHERE id = ?';
    getApi(querySql,values,res);
});

更新接口

// 更新接口 
router.put('/', (req, res) => {
  // console.log(req,'req');
  const { title, description,id} = convertKeysToSnakeCase(req.body);
  const updatevalues = {title, description};
  let updateSql='UPDATE activities SET ? WHERE id = ?';
  updateApi(updateSql,[updatevalues, id],res);
});

删除接口

// 删除数据 DELETE请求处理程序  
router.delete('/:id', (req, res) => {
    // const {id} = req.body; // 从请求体中获取数据
    const id = req.params.id;
    const delsql = "DELETE FROM activities WHERE id = ?";
    delApi(delsql,[id],res);
});