node.js 如何在猫鼬中加入两个集合

How to join two collections in mongoose

提问by Lulzim

I have two Schema defined as below:

我有两个架构定义如下：

var WorksnapsTimeEntry = BaseSchema.extend({
 student: {
     type: Schema.ObjectId,
     ref: 'Student'
 },
 timeEntries: {
     type: Object
 }
 });

var StudentSchema = BaseSchema.extend({
firstName: {
    type: String,
    trim: true,
    default: ''
    // validate: [validateLocalStrategyProperty, 'Please fill in your first name']
},
lastName: {
    type: String,
    trim: true,
    default: ''
    // validate: [validateLocalStrategyProperty, 'Please fill in your last name']
},
displayName: {
    type: String,
    trim: true
},
municipality: {
    type: String
    }
});

And I would like to loop thru each student and show it's time entries. So far I have this code which is obviously not right as I still dont know how do I join WorksnapTimeEntry schema table.

我想遍历每个学生并显示它的时间条目。到目前为止，我有这段代码显然是不对的，因为我仍然不知道如何加入 WorksnapTimeEntry 模式表。

Student.find({ status: 'student' })
        .populate('student')
        .exec(function (err, students) {
            if (err) {
                return res.status(400).send({
                    message: errorHandler.getErrorMessage(err)
                });
            }
            _.forEach(students, function (student) {
               // show student with his time entries....
            });
            res.json(students);
        });

Any one knows how do I achieve such thing?

有谁知道我如何实现这样的目标？

回答by Talha Awan

As of version 3.2, you can use $lookupin aggregation pipeline to perform left outer join.

从 3.2 版开始，您可以在聚合管道中使用$lookup来执行左外连接。

Student.aggregate([{
    $lookup: {
        from: "worksnapsTimeEntries", // collection name in db
        localField: "_id",
        foreignField: "student",
        as: "worksnapsTimeEntries"
    }
}]).exec(function(err, students) {
    // students contain WorksnapsTimeEntries
});

回答by Blakes Seven

You don't want .populate()here but instead you want two queries, where the first matches the Studentobjects to get the _idvalues, and the second will use $into match the respective WorksnapsTimeEntryitems for those "students".

您不想要.populate()这里，而是想要两个查询，其中第一个匹配Student对象以获取_id值，第二个将用于$in匹配WorksnapsTimeEntry这些“学生”的相应项目。

Using async.waterfalljust to avoid some indentation creep:

使用async.waterfall只是为了避免一些缩进蔓延：

async.waterfall(
    [
        function(callback) {
          Student.find({ "status": "student" },{ "_id": 1 },callback);
        },
        function(students,callback) {
            WorksnapsTimeEntry.find({
                "student": { "$in": students.map(function(el) {
                    return el._id
                })
            },callback);
        }
    ],
    function(err,results) {
       if (err) {
          // do something
       } else {
          // results are the matching entries
       }
    }
)

If you really must, then you can .populate("student")on the second query to get populated items from the other table.

如果你真的必须，那么你可以.populate("student")在第二个查询中从另一个表中获取填充的项目。

The reverse case is to query on WorksnapsTimeEntryand return "everything", then filter out any nullresults from .populate()with a "match" query option:

相反的情况是查询WorksnapsTimeEntry并返回“所有内容”，然后使用“匹配”查询选项过滤掉任何null结果.populate()：

WorksnapsTimeEntry.find().populate({
    "path": "student",
    "match": { "status": "student" }
}).exec(function(err,entries) {
   // Now client side filter un-matched results
   entries = entries.filter(function(entry) {
       return entry.student != null;
   });
   // Anything not populated by the query condition is now removed
});

So that is not a desirable action, since the "database" is not filtering what is likely the bulk of results.

所以这不是一个理想的操作，因为“数据库”没有过滤可能的大部分结果。

Unless you have a good reason not to do so, then you probably "should" be "embedding" the data instead. That way the properties like "status" are already available on the collection and additional queries are not required.

除非您有充分的理由不这样做，否则您可能“应该”“嵌入”数据。这样，像"status"这样的属性已经在集合中可用，并且不需要额外的查询。

If you are using a NoSQL solution like MongoDB you should be embracing it's concepts, rather than sticking to relational design principles. If you are consistently modelling relationally, then you might as well use a relational database, since you won't be getting any benefit from the solution that has other ways to handle that.

如果您使用像 MongoDB 这样的 NoSQL 解决方案，您应该接受它的概念，而不是坚持关系设计原则。如果您始终以关系建模，那么您不妨使用关系数据库，因为您不会从具有其他处理方式的解决方案中获得任何好处。