egg(28)--mongoose使用聚合管道

文件内容

导入数据

db.order.insert({"order_id":"1","uid":10,"trade_no":"111","all_price":100,"all_num":2})
db.order.insert({"order_id":"2","uid":7,"trade_no":"222","all_price":90,"all_num":2})
db.order.insert({"order_id":"3","uid":9,"trade_no":"333","all_price":20,"all_num":6})
db.order_item.insert({"order_id":"1","title":"商品鼠标 1","price":50,num:1})
db.order_item.insert({"order_id":"1","title":"商品键盘 2","price":50,num:1})
db.order_item.insert({"order_id":"1","title":"商品键盘 3","price":0,num:1})
db.order_item.insert({"order_id":"2","title":"牛奶","price":50,num:1})
db.order_item.insert({"order_id":"2","title":"酸奶","price":40,num:1})
db.order_item.insert({"order_id":"3","title":"矿泉水","price":2,num:5})
db.order_item.insert({"order_id":"3","title":"毛巾","price":10,num:1})

db.js

var mongoose = require('mongoose');
mongoose.connect('mongodb://eggadmin:[email protected]:27017/eggcms',{ useNewUrlParser: true },function(err){
    if(err){
        console.log(err)
        return
    }
    console.log("数据库连接成功")
})

module.exports = mongoose;

order.js

var mongoose = require('./db.js');
var OrderSchema = mongoose.Schema({
    order_id:{
        type:String,
    },
    uid:Number,
    trade_no:String,
    all_price:Number,
    all_num:Number,
})

var OrderSchema = mongoose.model('Order',OrderSchema,'order');
module.exports = OrderSchema

order_item.js

var mongoose = require('./db.js');
var OrderItemSchema = mongoose.Schema({
    order_id:{
        type:String,
    },
    title:String,
    price:Number,
    num:Number,
})

var OrderItemSchema = mongoose.model('OrderItem',OrderItemSchema,'order_item');
module.exports = OrderItemSchema

关联查询

app.js

var OrderModel = require('./order.js');
OrderModel.aggregate([{
    $lookup:{
        from:"order_item",
        localField:"order_id",
        foreignField:"order_id",
        as:"item"
    }
}],function(err,docs){
    if(err){
        console.log(err);
        return;
    }
    console.log(JSON.stringify(docs))
})

$project 修改文档的结构

可以用来重命名，增加或者删除文档中的字段
要求查找 order 只返回文档中 trade_no 和 all_price 字段

app.js

OrderModel.aggregate([
{
    $project:{
        trade_no:1,
        all_price:1
    }
}
], function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    console.log(docs)
})

$match,筛选

得到总价格>90的数据

app.js

var OrderModel = require('./order.js');
var OrderItemModel = require('./order_item.js');
OrderModel.aggregate([{
    $lookup: {
        from: "order_item",
        localField: "order_id",
        foreignField: "order_id",
        as: "item"
    },
}, {
    $match: {
        "all_price": {
            $gte: 90
        }
    }
}], function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    // console.log(docs)
    console.log(JSON.stringify(docs))
})

$group

将集合中的文档进行分组，可用于统计结果。
统计每个订单的订单数量，按照订单号分组

app.js

OrderItemModel.aggregate([{
    $group:{
        _id:"$order_id",
        total:{$sum:"$sum"}
    }
}],function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    // console.log(docs)
    console.log(JSON.stringify(docs))
})

$sort 排序

1为升序，-1为降序

app.js

OrderModel.aggregate([
{
    $project:{
        trade_no:1,
        all_price:1
    }
},
{
    $sort:{"all_price":1}
}
], function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    console.log(docs)
})

$limit 限制

只查一条

app.js

OrderModel.aggregate([
{
    $project:{
        trade_no:1,
        all_price:1
    }
},

{
    $sort:{"all_price":1}
},
{
    $limit:1
}
], function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    console.log(docs)
})

$skip 跳过

app.js

OrderModel.aggregate([
{
    $project:{
        trade_no:1,
        all_price:1
    }
},

{
    $sort:{"all_price":1}
},
{
    $skip:1
}
], function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    console.log(docs)
})

根据order_item,查询order的信息

查询order_item，找出商品名称是酸奶的商品，酸奶这个商品对应的订单的订单号和订单总价格等信息

第一种方式

app.js

var OrderModel = require('./order.js');
var OrderItemModel = require('./order_item.js');
OrderItemModel.find({
    "_id": "5bffa9bbd8cb3dff1d3d6658"
}, function (err, docs) {
    var order_item = JSON.parse(JSON.stringify(docs));
    var order_id = order_item[0].order_id;

    OrderModel.find({
        "order_id": order_id
    }, function (err, order) {
        order_item.order_info = order[0];
        console.log(order_item)
    })
})

第二种方式

app.js

var OrderItemModel = require('./order_item.js');
var mongoose = require("mongoose")
OrderItemModel.aggregate([{
    $lookup: {
        from: 'order',
        localField: "order_id",
        foreignField: "order_id",
        as: "order_info"
    }
}, {
    $match: {
        "_id":mongoose.Types.ObjectId('5bffa9bbd8cb3dff1d3d6658')
    }
}], function (err, docs) {
    if (err) {
        console.log(err);
        return;
    }
    console.log(JSON.stringify(docs))
})