本文分享如何通过 mongoose 的 population 实现关联表查询, population 的 api 可参考这里。
本文demo源码.
1.初始化两个schema和model。
/**
* department schema
**/
var departmentSchema = new Schema({
id: String, //部门编号
name: String //名称
});
/**
* employee schema
**/
var employeeSchema = new Schema({
id: Number, //工号
name: String, //姓名
sex: Number, //性别
age: Number, //年龄
dep: {
type: Schema.Types.ObjectId,
ref: 'department'
}
});
/**
* model
**/
var employee = mongodb.mongoose.model("employee", employeeSchema);
var department = mongodb.mongoose.model('department', departmentSchema);
employee 的属性 dep,对应model department. ref 表示关联 department model.
被关联的 model 的 type 必须是 ObjectId, Number, String, 和 Buffer.
2.初始化数据。
//部门表
db.departments.insert({"id":"1001","name":"市场部"});
db.departments.insert({"id":"1002","name":"销售部"});
db.departments.insert({"id":"1003","name":"行政部"});
//职工表
db.employees.insert({"id":"001","name":"jack","sex":1,"age":23,"dep":"593667c4ec59b38aefdc25b5"});
db.employees.insert({"id":"002","name":"tom","sex":2,"age":23,"dep":"593667c4ec59b38aefdc25b6"});
db.employees.insert({"id":"003","name":"kan","sex":1,"age":43,"dep":"593667c4ec59b38aefdc25b5"});
db.employees.insert({"id":"004","name":"julis","sex":2,"age":23,"dep":"593667c4ec59b38aefdc25b7"});
db.employees.insert({"id":"005","name":"michael","sex":1,"age":43,"dep":"593667c4ec59b38aefdc25b5"});
db.employees.insert({"id":"006","name":"jordan","sex":1,"age":25,"dep":"593667c4ec59b38aefdc25b5"});
注意此处的数据初始化顺序。必须先初始化部门表,然后初始化员工表。
3.使用population关联查询
employee.find({}) //查询所有的employees表中的数据,并将结果返回给populate
.populate({ path: 'dep', select: { name: 1 } }) //上述结果集合中的dep字段用departments表中的name字段填充
.exec(function(err, obj) {
callback(err, obj);
});
3.查询结果展示
渲染模版(ejs语法)及渲染语句:
res.render('list', { employeeList: obj });//obj为第三部中查询结果
<table class="table table-bordered tab-content-center">
<thead>
<tr>
<th>职工ID</th>
<th>姓名</th>
<th>性别</th>
<th>年龄</th>
<th>部门</th>
</tr>
</thead>
<tbody>
<% for(var i=0; i<employeeList.length; i++) {%>
<tr>
<td><%= employeeList[i]._doc.id %></td>
<td><%= employeeList[i]._doc.name %></td>
<td>
<% if(employeeList[i]._doc.sex == 1) { %>
男
<% } else { %>
女
<% } %>
</td>
<td><%= employeeList[i]._doc.age %></td>
<td><%= employeeList[i]._doc.dep.name %></td>
</tr>
<% } %>
</tbody>
</table>
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。