场景：商品列表中包含订单统计信息,商品和订单是一对多的关系
注： 这里把商品和订单分成两个集合，仅做演示！具体场景，具体分析
image
插入数据
创建goods和orders集合
goods
db.goods.insert([
{name: 'iphone XS', price: 8999},
{name: '小米 9', price: 3299},
{name: '华为 p30 Pro', price: 4988}
])
image
orders
/**
* goodsId为刚才goods生成的_id
* idDelete: 0 未删除 1 已删除
*/
db.orders.insert([
{sum: 1, goodsId: ObjectId('5d522fd20ec3b11bf17a91e2'), isDelete: 0},
{sum: 2, goodsId: ObjectId('5d522fd20ec3b11bf17a91e2'), isDelete: 0},
{sum: 1, goodsId: ObjectId('5d522fd20ec3b11bf17a91e2'), isDelete: 1},
{sum: 1, goodsId: ObjectId('5d5230870ec3b11bf17a91e4'), isDelete: 0},
{sum: 1, goodsId: ObjectId('5d5230870ec3b11bf17a91e5'), isDelete: 0},
{sum: 1, goodsId: ObjectId('5d5230870ec3b11bf17a91e5'), isDelete: 1},
])
// 在mongoose的schame中定义为：
goodsId:{
type: mongoose.Schema.ObjectId,
ref: 'Article'
}
image
查询
1、查询某一个商品的所有订单
// 查询语句
db.orders.find({goodsId: ObjectId("5d522fd20ec3b11bf17a91e2")})
// 结果
// 1
{
"_id": ObjectId("5d5376580ec3b11bf17a91ec"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
// 2
{
"_id": ObjectId("5d5376580ec3b11bf17a91ed"),
"sum": 2,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
// 3
{
"_id": ObjectId("5d5376580ec3b11bf17a91ee"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 1
}
// 过滤已删除的订单
db.orders.find({goodsId: ObjectId("5d522fd20ec3b11bf17a91e2"), isDelete: 0})
// 结果
// 1
{
"_id": ObjectId("5d5376580ec3b11bf17a91ec"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
// 2
{
"_id": ObjectId("5d5376580ec3b11bf17a91ed"),
"sum": 2,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
2、查询所有商品对应的所有订单
这里使用的是MongoDB中的aggregate
查询所有商品以及对应的订单
// 关联查询
db.goods.aggregate([
{
$lookup: {
from: "orders", // 要关联查询的集合
localField: "_id", // goods集合中的_id
foreignField: "goodsId", // 要查询的集合的 关联id
as: "child"
}
}
])
// 结果
// 1
{
"_id": ObjectId("5d522fd20ec3b11bf17a91e2"),
"name": "iphone XS",
"price": 8999,
"child": [
{
"_id": ObjectId("5d5376580ec3b11bf17a91ec"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
},
{
"_id": ObjectId("5d5376580ec3b11bf17a91ed"),
"sum": 2,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
},
{
"_id": ObjectId("5d5376580ec3b11bf17a91ee"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 1
}
]
}
// 2
{
"_id": ObjectId("5d5230870ec3b11bf17a91e4"),
"name": "小米 9",
"price": 3299,
"child": [
{
"_id": ObjectId("5d5376580ec3b11bf17a91ef"),
"sum": 1,
"goodsId": ObjectId("5d5230870ec3b11bf17a91e4"),
"isDelete": 0
}
]
}
// 3
{
"_id": ObjectId("5d5230870ec3b11bf17a91e5"),
"name": "华为 p30 Pro",
"price": 4988,
"child": [
{
"_id": ObjectId("5d5376580ec3b11bf17a91f0"),
"sum": 1,
"goodsId": ObjectId("5d5230870ec3b11bf17a91e5"),
"isDelete": 0
},
{
"_id": ObjectId("5d5376580ec3b11bf17a91f1"),
"sum": 1,
"goodsId": ObjectId("5d5230870ec3b11bf17a91e5"),
"isDelete": 1
}
]
}
查询所有商品以及对应的订单数
// 关联查询
db.goods.aggregate([
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "goodsId",
as: "child"
}
},
{
$project: {
name:1, // 筛选要返回的数据，值为：0不返回，1返回
price:1,
orderNm: {
"$size": "$child"
}
}
}
])
//结果
// 1
{
"_id": ObjectId("5d522fd20ec3b11bf17a91e2"),
"name": "iphone XS",
"price": 8999,
"orderNm": NumberInt("3")
}
// 2
{
"_id": ObjectId("5d5230870ec3b11bf17a91e4"),
"name": "小米 9",
"price": 3299,
"orderNm": NumberInt("1")
}
// 3
{
"_id": ObjectId("5d5230870ec3b11bf17a91e5"),
"name": "华为 p30 Pro",
"price": 4988,
"orderNm": NumberInt("2")
}
查询单价大于5000商品以及对应的订单
// 关联查询，加入删选条件
db.goods.aggregate([
{
$match: {
price: {
$gt : 5000
}
}
},
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "goodsId",
as: "child"
}
}
])
查询所有商品以及未删除的订单，是对关联集合的筛选
因为是对关联集合的筛选，所以不能简单通过
match去处理
db.goods.aggregate([
{
$lookup: {
from: "orders",
let: {
isDelete: "$isDelete", // 定义要筛选的条件
id: "$_id", // 当前集合的_id
},
pipeline: [{
"$match": {
isDelete: 0, // 筛选条件，未删除的订单
$expr: {
$eq: ["$$id","$goodsId"] // 商品对应的订单
}
},
}],
as: "child",
}
}
])
// 结果
// 1
{
"_id": ObjectId("5d522fd20ec3b11bf17a91e2"),
"name": "iphone XS",
"price": 8999,
"child": [
{
"_id": ObjectId("5d5376580ec3b11bf17a91ec"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
},
{
"_id": ObjectId("5d5376580ec3b11bf17a91ed"),
"sum": 2,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
]
}
// 2
{
"_id": ObjectId("5d5230870ec3b11bf17a91e4"),
"name": "小米 9",
"price": 3299,
"child": [
{
"_id": ObjectId("5d5376580ec3b11bf17a91ef"),
"sum": 1,
"goodsId": ObjectId("5d5230870ec3b11bf17a91e4"),
"isDelete": 0
}
]
}
// 3
{
"_id": ObjectId("5d5230870ec3b11bf17a91e5"),
"name": "华为 p30 Pro",
"price": 4988,
"child": [
{
"_id": ObjectId("5d5376580ec3b11bf17a91f0"),
"sum": 1,
"goodsId": ObjectId("5d5230870ec3b11bf17a91e5"),
"isDelete": 0
}
]
}
