場景：商品列表中包含訂單統(tǒng)計(jì)信息,商品和訂單是一對多的關(guān)系
注： 這里把商品和訂單分成兩個(gè)集合舆吮，僅做演示揭朝！具體場景，具體分析
image
插入數(shù)據(jù)
創(chuàng)建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色冀、查詢某一個(gè)商品的所有訂單
// 查詢語句
db.orders.find({goodsId: ObjectId("5d522fd20ec3b11bf17a91e2")})
// 結(jié)果
// 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})
// 結(jié)果
// 1
{
"_id": ObjectId("5d5376580ec3b11bf17a91ec"),
"sum": 1,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
// 2
{
"_id": ObjectId("5d5376580ec3b11bf17a91ed"),
"sum": 2,
"goodsId": ObjectId("5d522fd20ec3b11bf17a91e2"),
"isDelete": 0
}
2潭袱、查詢所有商品對應(yīng)的所有訂單
這里使用的是MongoDB中的aggregate
查詢所有商品以及對應(yīng)的訂單
// 關(guān)聯(lián)查詢
db.goods.aggregate([
{
$lookup: {
from: "orders", // 要關(guān)聯(lián)查詢的集合
localField: "_id", // goods集合中的_id
foreignField: "goodsId", // 要查詢的集合的 關(guān)聯(lián)id
as: "child"
}
}
])
// 結(jié)果
// 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
}
]
}
查詢所有商品以及對應(yīng)的訂單數(shù)
// 關(guān)聯(lián)查詢
db.goods.aggregate([
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "goodsId",
as: "child"
}
},
{
$project: {
name:1, // 篩選要返回的數(shù)據(jù)，值為：0不返回锋恬，1返回
price:1,
orderNm: {
"$size": "$child"
}
}
}
])
//結(jié)果
// 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")
}
查詢單價(jià)大于5000商品以及對應(yīng)的訂單
// 關(guān)聯(lián)查詢屯换，加入刪選條件
db.goods.aggregate([
{
$match: {
price: {
$gt : 5000
}
}
},
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "goodsId",
as: "child"
}
}
])
查詢所有商品以及未刪除的訂單，是對關(guān)聯(lián)集合的篩選
因?yàn)槭菍﹃P(guān)聯(lián)集合的篩選与学，所以不能簡單通過
match去處理
db.goods.aggregate([
{
$lookup: {
from: "orders",
let: {
isDelete: "$isDelete", // 定義要篩選的條件
id: "$_id", // 當(dāng)前集合的_id
},
pipeline: [{
"$match": {
isDelete: 0, // 篩選條件彤悔，未刪除的訂單
$expr: {
$eq: ["$$id","$goodsId"] // 商品對應(yīng)的訂單
}
},
}],
as: "child",
}
}
])
// 結(jié)果
// 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
}
]
}
