mongodb联表查询

电玩女神 2022-11-15 05:53 230阅读 0赞

## 数据库表 ##

### 表一: 数据记录表 ###

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 关联查询
    
    <table> 
     <thead> 
      <tr> 
       <th>参数</th> 
       <th>赋值</th> 
       <th>说明</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>from</td> 
       <td>“docs”</td> 
       <td>需要关联查询的表 此处查询的事主表docs</td> 
      </tr> 
      <tr> 
       <td>localField</td> 
       <td>“_id.name”</td> 
       <td>匹配查询数据的表的文档名称字段传参“_id.name”的原因是：aggregate管道模式数组上一个参数是下一个参数的入参 当前表文档名称关键字已经被定义到_id对象里面了</td> 
      </tr> 
      <tr> 
       <td>foreignField</td> 
       <td>“name”</td> 
       <td>匹配要联表的文档名称关键字 即docs表文档 名称关键字</td> 
      </tr> 
      <tr> 
       <td>as</td> 
       <td>“child”</td> 
       <td>把当前匹配到的数据输出到as定义的别名 child里面 child是一个数组</td> 
      </tr> 
     </tbody> 
    </table>
    
    注意：以摸一个关键字 如：文档名称 来聚合数据是 首先聚合出来的数据每个关键字只能有一条数据 否则联表时无法匹配出关键字对应的主表数据
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" : "前端问题反馈"
              }
          ]
      }
    ]