Koa & Mongoose & Vue实现前后端分离--12联表查询

上节回顾

• 图片上传 & 存储 & 访问

工作内容

• 审批的增删改查
• 更新、删除数据时，校验当前用户是不是数据所属人
• 通过ref查询另一表中的数据

准备工作

• npm i -S moment //分别切换到/server、/client目录下安装
• 把服务端/users的路由和处理逻辑拷一份，改改名
• 这里主要是罗列代码

业务逻辑

服务端代码

• 路由处理逻辑

// 新建文件：server/control/approves.js
const moment =  require('moment');
const approveModel =  require('../model/approve');
const userModel =  require('../model/user');

async  function  list(ctx) {
    // 【通常做法，由多的一方过滤单一的一方】
    // 文档：http://www.mongoosejs.net/docs/populate.html#refs-to-children
    try {
        const approves = await approveModel.find({
            author: ctx.state.auth.id
        });
        ctx.body = {
            code:  '200',
            data: approves,
            msg:  '查询成功'
        };
    } catch (err) {
        ctx.body = {
            code:  '403',
            data: {
                error: err
            },
            msg:  '查询失败'
        }
    }
}

async  function  get(ctx){
    const { id } = ctx.params;
    try {
        const approve = await approveModel.findOne({
            _id: id
        });
        if(approve) {
            ctx.body = {
                code:  '200',
                data: approve,
                msg:  '成功'
            }
        } else {

            ctx.body = {
                code:  '403',
                data: null,
                msg:  '找不到数据创建人'
            }
        }
    } catch (err) {
        ctx.body = {
            code:  '404',
            data: {
                _id: id
            },
            msg:  '获取失败，请核对数据id'
        }
    }
}

async  function  create(ctx) {
    const { id: loginerId } = ctx.state.auth;
    const payload = ctx.request.body;
    const curtime =  moment().format('x');
    try {
        const newApprove = await  new approveModel({
            ...payload,
            status: false,
            author: loginerId,
            modifier: loginerId,
            createtime: curtime,
            latesttime: curtime,
        }).save();
        ctx.body = {
            code:  '200',
            data: newApprove,
            msg:  '新建成功'
        }
    } catch (err) {
        ctx.body = {
            code:  '403',
            data: null,
            msg:  '新建失败'
        }
    }
}

async  function  update(ctx){
    const payload = ctx.request.body;
    const { id } = ctx.params;
    const curtime =  moment().format('x');
    try {
        await approveModel.updateOne(
            {
                _id: id
            },
            {
                ...payload,
                latesttime: curtime
            }
        ).exec();
        ctx.body= {
            code:  '200',
            data: {
                _id: id
            },
            msg:  '更新成功'
        }
    } catch (err) {
        ctx.body = {
            code:  '404',
            data: payload,
            msg:  '更新失败，请核对数据id'
        }
    }
}

async  function  drop(ctx){
    const { id } = ctx.params;
    await approveModel.findOneAndRemove({
        _id: id
    })
    ctx.body = {
        code:  '200',
        data: null,
        msg:  '删除成功'
    }
} 

module.exports = {
    list,
    get,
    create,
    update,
    drop
}

• 路由拦截

  • 在进行敏感操作，如更新、删除，先判断当前用户是否是数据所属人checkLoginer

// 更新文件：server/router/approves.js
const Router =  require('@koa/router');
const approveModel =  require('../model/approve');
const controls =  require('../control/approves');
const routerUtils =  require('../utils/router');
const {
    list,
    get,
    create,
    update,
    drop
} = controls;
const router =  new Router({
    prefix:  '/approves'
});

async  function  checkLoginer  (ctx, next) {
    const { id } = ctx.params;
    const approve = await approveModel.findOne({
        _id: id
    });
    if (approve.author != ctx.state.auth.id) {
        ctx.body = {
            code:  '403',
            data: null,
            msg:  '当前用户不是创建者'
        }
    } else {
        await  next()
    }
}
const routes = [
    {
        path:  '/',
        method:  'GET',
        handle: list
    },
    {
        path:  '/',
        method:  'POST',
        handle: create
    },
    {
        path:  '/:id',
        method:  'GET',
        handle: get
    },
    {
        path:  '/:id',
        method:  'PATCH',
        payload: {},
        handle: update,
        middlewares: [
            checkLoginer
        ]
    },
    {
        path:  '/:id',
        method:  'DELETE',
        handle: drop,
        middlewares: [
            checkLoginer
        ]
    }
]
routerUtils.register.call(router, routes);
module.exports = router;

• 更新路由配置文件

// 更新文件：server/router/index.js
const userRouter =  require('./users');
const assetsRouter =  require('./assets');
const approvesRouter =  require('./approves');

module.exports = [
    userRouter.routes(),
    userRouter.allowedMethods(),
    approvesRouter.routes(),
    approvesRouter.allowedMethods(),
    assetsRouter.routes(),
    assetsRouter.allowedMethods()
];

• 测试新建：[动态太大，传不上来]
• 测试查询

• 测试更新：

• 测试删除：

前端代码

// 更新文件：client/src/views/approve-panel/index.vue
<template\>

<div  class\="approve-module"\>

<div  class\="btns-wrap"\>

<el-button  type\="primary" @click\="handleApprove('increase')"\>新建</el-button\>

</div\>

<el-table

:data\="table.tableBody"

border

@selection-change\="handleSelectionChange"

style\="width: 100%"\>

<template  v-for\="header  in  table.tableHeader"\>

<el-table-column

v-if\="header.key === 'selection'"

:key\="header.key"

v-bind\="Object.assign({}, header.options, header.layout)"

\>

</el-table-column\>

<el-table-column

v-else

:key\="header.key"

:label\="header.metas.label"

v-bind\="Object.assign({}, header.options, header.layout)"\>

<template  slot-scope\="scope"\>

<span  v-if\="header.metas.type === 'text'"\>{{scope.row\[header.key\]}}</span\>

<el-button-group  v-else-if\="header.metas.type === 'button'"\>

<template  v-for\="btn  in  header.metas.value"\>

<el-button :class\="\`btn-${btn.key}\`" @click\="handleApprove(btn.key, scope.row)"  v-if\="btn.attributes.visible"  size\="small"  type\="text" :key\="btn.key"  v-bind\="btn.attributes"\>{{btn.label}}</el-button\>

</template\>

</el-button-group\>

<span  v-else\>{{header.metas.formatter(scope.row\[header.key\])}}</span\>

</template\>

</el-table-column\>

</template\>

</el-table\>

<el-dialog :title\="dialogForm.title" :visible.sync\="dialogForm.visible"\>

<el-form :inline\="false" :ref\="dialogForm.formRef" :model\="dialogForm.form" :rules\="dialogForm.rules"\>

<el-form-item  label\="名称"  prop\="name"\>

<el-input  v-model\="dialogForm.form.name"\></el-input\>

</el-form-item\>

<el-form-item  label\="分类"  prop\="category"\>

<el-select  v-model\="dialogForm.form.category"  placeholder\="请选择分类"\>

<el-option  label\="年假"  value\="1"\></el-option\>

<el-option  label\="病假"  value\="2"\></el-option\>

</el-select\>

</el-form-item\>

<el-form-item  label\="描述"  prop\="description"\>

<el-input  type\="textarea"  v-model\="dialogForm.form.description"\></el-input\>

</el-form-item\>

</el-form\>

<div  slot\="footer"  class\="dialog-footer"\>

<el-button @click\="cancelOperate"\>取 消</el-button\>

<el-button  type\="primary" @click\="submitForm"\>确 定</el-button\>

</div\>

</el-dialog\>

</div\>

</template\>

  

<script\>

import  moment  from  'moment'

import  http  from  '@/utils/http'

  

export  default  {

INITFORMDATA:  {

name:  '',

category:  '',

description:  '',

status:  false,

latesttime:  0,

createtime:  0,

author:  {},

},

methods:  {

async  init  ()  {

const  res  \=  await  http.get('/approves')

if (res.code  \===  '200') {

this.$set(this.table,  'tableBody',  res.data)

}

},

handleSelectionChange  (val)  {

console.log(val)

},

handleClick  (row)  {

console.log(row)

},

handleApprove  (type,  initData)  {

this\[\`${type}Handle\`\](initData)

},

cancelOperate  ()  {

this.dialogForm  \=  Object.assign(

{},

this.dialogForm,

{

visible:  false,

formRef:  '',

form:  this.$options.INITFORMDATA

}

)

},

increaseHandle  (initData  \=  {})  {

this.dialogForm  \=  Object.assign(

this.dialogForm,

{

visible:  true,

title:  '新建',

formRef:  'increaseForm'

}

)

},

editHandle  (initData  \=  {})  {

this.dialogForm  \=  Object.assign(

this.dialogForm,

{

visible:  true,

title:  '编辑',

formRef:  'editForm',

form:  {

...initData

}

}

)

},

dropHandle  (initData  \=  {})  {

this.$confirm('此操作将永久删除该数据, 是否继续?',  '提示',  {

confirmButtonText:  '确定',

cancelButtonText:  '取消',

type:  'warning'

}).then(async  ()  \=>  {

const  res  \=  await  http.delete(\`/approves/${initData.\_id}\`)

if (res.code  \===  '200') {

this.$message({

type:  'success',

message:  '删除成功!'

})

this.init()

}  else  {

throw(new Error('发生错误'))

}

}).catch((err)  \=>  {

this.$message({

type:  'info',

message:  err.message  ||  '已取消删除'

});

});

},

async  submitForm  ()  {

try  {

const  valid  \=  this.$refs\[this.dialogForm.formRef\].validate()

if (this.dialogForm.formRef  \===  'increaseForm') {

const  res  \=  await  http.post(

'/approves',

{

...this.dialogForm.form

}

)

if (res.code  \===  '200') {

this.$message({

type:  'success',

message:  '新建成功'

})

}  else  {

this.$message({

type:  'error',

message:  res.msg

})

}

}  else  {

const  {  \_id,  name,  category,  description,  status}  \=  this.dialogForm.form

const  res  \=  await  http.patch(

\`/approves/${\_id}\`,

{

name,

category,

description,

status

}

)

if (res.code  \===  '200') {

this.$message({

type:  'success',

message:  '更新成功'

})

}  else  {

this.$message({

type:  'error',

message:  res.msg

})

}

}

this.cancelOperate()

this.init()

}  catch (err) {

console.log(err)

}

}

},

data  ()  {

return  {

table:  {

tableHeader: \[

{

key:  'selection',

metas:  {

},

options:  {

type:  'selection'

},

layout:  {

}

},

{

key:  'name',

metas:  {

label:  '名称',

type:  'text'

},

options:  {

'show-overflow-tooltip':  true

},

layout:  {

width:  200

}

},

{

key:  'category',

metas:  {

label:  '类别',

type:  'text'

},

layout:  {

width:  60

}

},

{

key:  'description',

metas:  {

label:  '描述',

type:  'text'

},

options:  {

'show-overflow-tooltip':  true

},

layout:  {

}

},

{

key:  'author',

metas:  {

label:  '创建人',

type:  'object',

formatter  (val)  {

return  val.alias  ||  val.account

}

},

layout:  {

width:  80

}

},

{

key:  'createtime',

metas:  {

label:  '创建时间',

type:  'timestamp',

formatter  (val)  {

return  moment(val).format('YYYY-MM-DD hh:mm')

}

},

layout:  {

width:  150

}

},

{

key:  'latesttime',

metas:  {

label:  '更新时间',

type:  'timestamp',

formatter  (val)  {

return  moment(val).format('YYYY-MM-DD HH:mm')

}

},

layout:  {

width:  150

}

},

{

key:  'operate',

metas:  {

label:  '操作',

type:  'button',

value: \[

{

key:  'edit',

label:  '编辑',

attributes:  {

visible:  true,

disabled:  false

}

},

{

key:  'drop',

label:  '删除',

attributes:  {

visible:  true,

disabled:  false

}

}

\]

},

layout:  {

fixed:  'right',

width:  100

}

}

\],

tableBody: \[\]

},

dialogForm:  {

visible:  false,

title:  '',

formRef:  '',

form:  {

name:  '',

category:  '',

description:  '',

status:  false

},

rules:  {

name: \[

{  required:  true,  message:  '请输入名称',  trigger:  'blur'  }

\],

category: \[

{  required:  true,  message:  '请选择分类',  trigger:  'change'  }

\],

description: \[\]

}

}

}

},

async  created  ()  {

this.init()

}

}

</script\>

<style  lang\="scss"  scoped\>

@import  '~@/stylesheets/layout.scss';

@import  './index.scss';

</style\>

• 这代码符号被转义…格式…建议复制到编辑器内，全局替换并格式化一下。

// 新建文件：client/src/views/approve-panel/index.scss
.approve-module  {
    .btns-wrap  {
        margin-bottom:  20px;
        @include  flex($content:  flex-end);
    }
    /deep/ {
        .btn-drop  {
            margin-left:  16px;
        }
    }
}

展示效果：

这里“创建人”是具体的用户名，上传和存储的时候都是用户ID，如何通过用户ID查找用户具体信息：

// 更新文件：server/control/approves.js
...
async function list (ctx) {
  ...
  try {
    const approves = await approveModel.find({
        author: ctx.state.auth.id
    }).populate('author');
    ctx.body = {
        code:  '200',
        data: approves,
        msg:  '查询成功'
    };
  }
  ...
}
...
async function get (ctx) {
  const { id } = ctx.params;
  try {
    const approve = await approveModel.findOne({
      _id: id
  }).populate({
    path:  'author',
    select:  '+avatar +alias +telephone +email +department +job +role +_id +__v'
  }).exec();
  ...
}

• 使用populate与server/model/approve.js文件中的

author: {
    type:  Schema.Types.ObjectId,
    ref:  'User',
    required: true
},

对应使用，可查出用户信息。
测试结果：[动态太大，传不上来]

发现，描述没有显示出来，这是因为description没有被查出，修改server/control/approves.js中list部分代码.populate('author').select('+description');

参考文档

一对多关系