Mongoose 两个表关联查询aggregate 以及 Mongoose中获取ObjectId

Mongoose 两个表关联查询aggregate

通常两个表关联查询的时候,是一种一对多的关系,比如订单与订单详情就是一对多的关系,一个订单下面有多个商品

数据模拟

首先我们先将数据模拟出来,先选择数据库

use eggcms
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})

代码演示:使用Mongoose来实现order集合和order_item集合的关联查询

Mongoose中aggregate管道的语法跟原生MongoDB的语法是一样的

db.order.aggregate([
  {
    $lookup:
      {
        from: "order_item",
        localField: "order_id",
        foreignField: "order_id",
        as: "items"
      }
  }
])

第一步:创建model文件夹并且创建db.js(连接数据库)

const mongoose = require('mongoose');

mongoose.connect('mongodb://127.0.0.1:27017/eggcms', { useNewUrlParser: true }, (err) => {
  if(err){
    return console.log(err);
  }
  console.log('数据库连接成功')
});

module.exports = mongoose

第二步:分别创建order.js和order_item.js这两个model模块

var mongoose = require('./db.js');

var OrderSchema=mongoose.Schema({
  order_id: String,
  uid: Number,
  trade_no: String,
  all_price: Number,
  all_num: Number
})

module.exports = mongoose.model('Order',OrderSchema,'order');
var mongoose = require('./db.js');


var OrderItemSchema = mongoose.Schema({
  order_id: String,
  title: String,
  price: Number,
  num: Number
})

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

第三步:在app.js中进行两个集合的关联查询

let OrderModel = require('./model/order.js');

// order表关联order_item
OrderModel.aggregate([
  {
    $lookup:
      {
        from: "order_item",
        localField: "order_id",
        foreignField: "order_id",
        as: "items"
      }
  }
], (err, docs) => {
  if(err){
    return console.log(err)
  }

  console.log(JSON.stringify(docs))
})

将查询出来的数据复制出来解析成JSON格式会更清晰,找一个在线格式化的网址即可,实际项目中不需要

 

 

 

Mongoose 两个表关联查询aggregate练习

需求:查询order_item,找出商品名称是酸奶的商品,酸奶这个商品对应的订单的订单号以及订单的总价格

第一种实现方式

思路:首先通过酸奶这个商品的_id查询order_item集合,找到这个商品,并且获取商品中的order_id,然后再通过order_id去查询order集合

let OrderItemModel = require('./model/order_item.js');

let OrderModel = require('./model/order.js');

OrderItemModel.find({"_id":"5cde69c24a988180bdf060b0"}, (err,docs) => {

  // console.log(docs);

  let order_item = JSON.parse(JSON.stringify(docs));

  let order_id = order_item[0].order_id;


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

第二种实现方式 

思路:直接使用$match:条件匹配,然后进行两个表关联查询

mongoose中获取ObjectId (mongoose.Types.ObjectId)

let OrderItemModel = require('./model/order_item.js');
let mongoose = require('mongoose');

OrderItemModel.aggregate([
  {
    $lookup:
      {
        from: "order",
        localField: "order_id",
        foreignField: "order_id",
        as: "order_info"
      }
  },{
    $match:{_id: mongoose.Types.ObjectId('5cde69c24a988180bdf060b0')}
  }

], (err,docs) => {
  if(err){
    console.log(err)
    return;
  }
  console.log(JSON.stringify(docs))
})

 

转载于:https://www.cnblogs.com/LO-ME/p/10882172.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值