乐趣区

关于前端:mongodb联表查询

jiezi

数据库表

表一: 数据记录表

var mongoose = require('mongoose'),
    Schema = mongoose.Schema;

var DocViewSchema = new Schema({
    realName: String, // 实在姓名
    userName: String, //OA 账号
    count: Number, // 文档统计
    docName: String, // 文档项目名称
    docType: String, // 文档我的项目类型
    orgPath: String, // 所属部门的组织门路
    orgGroup: String, // 所属事业群
    orgName: String // 所属小组
    time: String,
});

module.exports = mongoose.model('Doc_view', DocViewSchema);

表二：文档主表

var mongoose = require('mongoose'),
    Schema = mongoose.Schema;
var docsSchema = new Schema({
    docType:String, // 文档类型
    user_name: String, // 零碎用户
    realName: String, // 用户中文姓名
    description: String, // 文档形容
    gitUrl:String,// 操作的文档源近程地址
    displayName: String, // 文档展现名
    name:String,// 文档名称
    logo:String,
    owner:String,// 文档创建人
    developer:Array, // 开发者
    realName: String,// 实在姓名
    toTop:Date,// 是否置顶
    dest: String, //vuepress 类型文档的输入目录
    docKey: String, // 束缚文档为 public（公开）、private（公有）类型的关键字
    privateMember: Array, // 存储公有文档可查看的人员范畴
    createTime: {
        type: Date,
        default: Date.now
    },
    opendFiles:Array,
    orgName: String, // 所属部门
    orgPath: String, // 所属部门的组织门路
    orgGroup: String, // 所属事业群
    docCollection: Array,
    newGit: Boolean // 标识是否是在零碎上构建的新我的项目
});
module.exports = mongoose.model('docs', docsSchema);

表数据

表一: 展现数据

{"_id" : ObjectId("603678000f6ec514b4b8d02f"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "abgFeedback",
    "userName" : "zhangsan",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "张三",
    "count" : 10
}

/* 42 */
{"_id" : ObjectId("603678000f6ec514b4b8d030"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "bookgit",
    "userName" : "liuniu",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "刘牛",
    "count" : 1
}

/* 43 */
{"_id" : ObjectId("603678000f6ec514b4b8d031"),
    "__v" : 0,
    "time" : "2021-02-24",
    "docName" : "testtestdocs",
    "userName" : "lisi",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "李四",
    "count" : 1
}

/* 44 */
{"_id" : ObjectId("6037c9800f6ec514b4b8d03c"),
    "__v" : 0,
    "time" : "2021-02-25",
    "docName" : "abgFeedback",
    "userName" : "zhangsan",
    "orgPath" : "******",
    "orgGroup" : "*****",
    "orgName" : "****",
    "realName" : "张三",
    "count" : 10
}

表二：文档数据


/* 45 */
{"_id" : ObjectId("5f6da851a18a783210da7a16"),
    "docType" : "vuepress",
    "name" : "abgFeedback",
    "gitUrl" : "*****/abgFeedback.git",
    "description" : "问题反馈收集问题",
    "owner" : "zhangsan",
    "orgGroup" : "*****",
    "orgName" : "*****",
    "orgPath" : "****",
    "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg",
    "newGit" : false,
    "toTop" : ISODate("2020-11-20T02:41:33.742Z"),
    "realName" : "龚成辉",
    "dest" : "abgFeedback",
    "opendFiles" : [],
    "createTime" : ISODate("2020-09-25T08:20:33.373Z"),
    "developer" : [],
    "__v" : 0,
    "docCollection" : [],
    "docKey" : "private",
    "privateMember" : ["lisi"]
}

联表查问

db.getCollection('doc_views').aggregate([
    {
        $match: {
            time: {
                $gte: "2020-03-29",
                $lte: "2021-03-29"
            }
        }
    },
    {
        $group: {
            _id: {"name": '$docName'},
            total: {$sum: "$count"}
        }
    },
    {
        $lookup: {
            from: "docs",
            localField: "_id.name",
            foreignField: "name",
            as: "child"
        }
    },
   {
       $project: {
           "child._id": false,
           "child.gitUrl": false,
           "child.description": false,
           "child.orgGroup": false,
           "child.orgPath": false,
           "child.newGit": false,
           "child.toTop": false,
           "child.docCollection": false,
           "child.opendFiles": false,
           "child.createTime": false,
           "child.logo": false,
           "child.orgName": false,
           "child.owner": false,
           "child.realName": false,
           "child.__v": false,
           "child.privateMember": false,
           "child.developer": false,
           "child.name": false
      }
    },
])
  1. $match 聚合匹配条件 这块是以 doc_views 表 time 字段匹配前端传入的起始工夫和截止时间段的所有数据

  2. $group 按某些指定的表达式对文档进行分组，而后将每个不同的分组的文档输入到下一阶段。输入文档蕴含一个 _id 字段，其中蕴含按关键字辨别的组

    1. 这里以文档名称 docName 为关键字进行分组统计
    2. 同时给 docName 定义了一个 name 的别名 并包装到_id 的对象上面
    3. 把所有以后时间段的同名文档进行合并并累计 count 字段输入 total 总数

  3. $lookup 关联查问

    参数 赋值 阐明
    from “docs” 须要关联查问的表 此处查问的事主表 docs
    localField “_id.name” 匹配查问数据的表的文档名称字段传参“_id.name”的起因是：aggregate 管道模式数组上一个参数是下一个参数的入参 以后表文档名称关键字曾经被定义到_id 对象外面了
    foreignField “name” 匹配要联表的文档名称关键字 即 docs 表文档 名称关键字
    as “child” 把以后匹配到的数据输入到 as 定义的别名 child 外面 child 是一个数组

    留神：以摸一个关键字 如：文档名称 来聚合数据是 首先聚合进去的数据每个关键字只能有一条数据 否则联表时无奈匹配出关键字对应的主表数据

  4. $project 可指定显示或者疏忽联表外面的某些字段 如上所述 false 示意不显示 true 示意显示

返回后果

// 疏忽字段的查问后果
[
  /* 22 */
  {
      "_id" : {"name" : "ces1234"},
      "total" : 5,
      "child" : [ 
          {
              "docType" : "gitbook",
              "docKey" : "private"
          }
      ]
  },

  /* 23 */
  {
      "_id" : {"name" : "abgFeedback"},
      "total" : 43,
      "child" : [ 
          {
              "docType" : "vuepress",
              "dest" : "abgFeedback",
              "docKey" : "private"
          }
      ]
  }
]
// 不疏忽字段的返回后果
[
  /* 22 */
  {
      "_id" : {"name" : "ces1234"},
      "total" : 5,
      "child" : [ 
          {"_id" : ObjectId("5da19272f76a861008c468ff"),
              "docType" : "gitbook",
              "name" : "ces1234",
              "gitUrl" : "***/docs/ces1234.git",
              "description" : "APP 前端组业务文档",
              "owner" : "zhangsan",
              "opendFiles" : [],
              "createTime" : ISODate("2019-10-12T08:44:34.986Z"),
              "developer" : ["lisi"],
              "__v" : 0,
              "orgPath" : "****",
              "orgName" : "****",
              "orgGroup" : "****",
              "toTop" : ISODate("2020-06-12T02:09:20.255Z"),
              "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg",
              "docCollection" : [],
              "docKey" : "private",
              "privateMember" : [],
              "realName" : "龚成辉",
              "displayName" : "测试项目"
          }
      ]
  },

  /* 23 */
  {
      "_id" : {"name" : "abgFeedback"},
      "total" : 43,
      "child" : [ 
          {"_id" : ObjectId("5f6da851a18a783210da7a16"),
              "docType" : "vuepress",
              "name" : "abgFeedback",
              "gitUrl" : "****/docs/abgFeedback.git",
              "description" : "问题反馈收集问题",
              "owner" : "gongchenghui",
              "orgGroup" : "****",
              "orgName" : "****",
              "orgPath" : "****",
              "logo" : "https://img.58cdn.com.cn/escstatic/fecar/pmuse/58doc/58logo.jpeg",
              "newGit" : false,
              "toTop" : ISODate("2020-11-20T02:41:33.742Z"),
              "realName" : "龚成辉",
              "dest" : "abgFeedback",
              "opendFiles" : [],
              "createTime" : ISODate("2020-09-25T08:20:33.373Z"),
              "developer" : [],
              "__v" : 0,
              "docCollection" : [],
              "docKey" : "private",
              "privateMember" : ["wangwu"],
              "displayName" : "前端问题反馈"
          }
      ]
  }
]
退出移动版