1. 查詢每個order訂單中的所有orderItem（多表關聯查詢）

order.js:

var mongoose = require('./db.js')var OrderSchema = mongoose.Schema({order_id: String,uid: Number,trade_no: String,all_price: Number,all_num: Number })var OrderModel = mongoose.model('Order',OrderSchema, 'order')module.exports = OrderModel

order_item.js:

var mongoose = require('./db.js')var OrderItemSchema = mongoose.Schema({order_id: String,title: String,price: Number,num: Number })var OrderItemModel = mongoose.model('OrderItem',OrderItemSchema, 'order_item')module.exports = OrderItemModel

app.js:

var OrderModel = require('./model/order.js')// var OrderItemModel = require('./model/order_item.js')// order表 關聯 order_item表// 查詢order表的數據 // OrderModel.find({}, function (err,data) { // if (err) { // console.log(err) // return; // } // console.log(data) // })OrderModel.aggregate([{$lookup: {from: "order_item",localField: "order_id",foreignField: "order_id",as: "items"}} ], function (err, data) {if (err) {console.log(err)return;}console.log(JSON.stringify(data)) })

多表查詢結果：

// 1 {"_id": ObjectId("606ecfbdbb390000fa004964"),"order_id": "1","uid": 10,"trade_no": "111","all_price": 100,"all_num": 2,"items": [{"_id": ObjectId("606ecfbdbb390000fa004967"),"order_id": "1","title": "商品鼠標 1","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004968"),"order_id": "1","title": "商品鍵盤 2","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004969"),"order_id": "1","title": "商品鍵盤 3","price": 0,"num": 1}] }// 2 {"_id": ObjectId("606ecfbdbb390000fa004965"),"order_id": "2","uid": 7,"trade_no": "222","all_price": 90,"all_num": 2,"items": [{"_id": ObjectId("606ecfbdbb390000fa00496a"),"order_id": "2","title": "牛奶","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa00496b"),"order_id": "2","title": "酸奶","price": 40,"num": 1}] }// 3 {"_id": ObjectId("606ecfbdbb390000fa004966"),"order_id": "3","uid": 9,"trade_no": "333","all_price": 20,"all_num": 6,"items": [{"_id": ObjectId("606ecfbdbb390000fa00496c"),"order_id": "3","title": "礦泉水","price": 2,"num": 5},{"_id": ObjectId("606ecfbdbb390000fa00496d"),"order_id": "3","title": "毛巾","price": 10,"num": 1}] }

2. 查詢每個order訂單中的所有orderItem（多表關聯查詢），并且篩選出每個order訂單的總價all_price>=90的訂單

app.js:

var OrderModel = require('./model/order.js')// var OrderItemModel = require('./model/order_item.js')// order表 關聯 order_item表// 查詢order表的數據 // OrderModel.find({}, function (err,data) { // if (err) { // console.log(err) // return; // } // console.log(data) // })OrderModel.aggregate([{$lookup: {from: "order_item",localField: "order_id",foreignField: "order_id",as: "items"}},{$match: {"all_price": { $gte: 90 }}} ], function (err, data) {if (err) {console.log(err)return;}console.log(JSON.stringify(data)) }) // 1 {"_id": ObjectId("606ecfbdbb390000fa004964"),"order_id": "1","uid": 10,"trade_no": "111","all_price": 100,"all_num": 2,"items": [{"_id": ObjectId("606ecfbdbb390000fa004967"),"order_id": "1","title": "商品鼠標 1","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004968"),"order_id": "1","title": "商品鍵盤 2","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa004969"),"order_id": "1","title": "商品鍵盤 3","price": 0,"num": 1}] }// 2 {"_id": ObjectId("606ecfbdbb390000fa004965"),"order_id": "2","uid": 7,"trade_no": "222","all_price": 90,"all_num": 2,"items": [{"_id": ObjectId("606ecfbdbb390000fa00496a"),"order_id": "2","title": "牛奶","price": 50,"num": 1},{"_id": ObjectId("606ecfbdbb390000fa00496b"),"order_id": "2","title": "酸奶","price": 40,"num": 1}] }

3. 查詢order_item 找出商品名稱是酸奶的商品對應的訂單的訂單號以及訂單的總價格

第一種實現方式：采用兩次查詢

第二種實現方式：采用表關聯查詢

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的Mongoose aggregate 多表关联查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。