我有两个模型,用户模型和时间表,我想用$ lookup和猫鼬把这两个模型结合起来。
用户(型号)
name: { type: String, required: true }, firstName: { String }, lastName: { String }, storeKey: { type: String, required: true }, avatar: String, birthday: String, phone: { type: String }, doc: String, email: { type: String }, password: { passwordHash: String, salt: String }, active: { type: Boolean, default: true }, deleted: { type: Boolean, default: false }, generalObservations: { type: String }, from: { type: String }, services: { type: Number, default: 0 }, no_shows: { type: Number, default: 0 }, // campos para integração integrationId: String }, { timestamps: true
时间表(型号)
store: { type: String, required: true }, customer: { id: { type: ObjectId }, name: { type: String, required: true }, avatar: String, phone: { type: String }, doc: { type: String }, }, employee: { id: { type: String, required: true }, name: { type: String, required: true }, avatar: String, }, service: { id: { type: String }, name: { type: String, required: true }, filters: [String] }, info: { channel: { type: String, required: true, default: 'app' }, id: String, name: String }, scheduleDate: { type: String, required: true }, scheduleStart: { type: String, required: true }, scheduleEnd: { type: String, required: true }, value: { type: Number, required: true }, comissionType: { type: String, default: '$' }, comissionValue: { type: Number, default: 0 }, status: { type: Number, required: true }, observation: String, paymentMethod: { type: Number, default: 0 }, paymentValue: String, paymentChange: String }, { timestamps: { createdAt: 'created', updatedAt: 'updated' }
现在我的查询使用猫鼬:
用户汇总
User.aggregate([{ $match: { storeKey: req.body.store, } }, { $group: { _id: { id: "$_id", name: "$name", cpf: "$cpf", phone: "$phone", email: "$email", birthday: "$birthday" }, totalServices: { $sum: "$services" } } }, { $lookup: { from: "schedule", localField: "_id.id", foreignField: "customer.id", as: "user_detail" } }
我的查询结果是一个空的array(user_detail),如下所示:
user_detail
查询结果:
{ "_id": { "id": "5bdb5b16ee9b7a4aa810bc62", "name": "Jonas com aniversário", "phone": "11984798494", "email": "j@jz.com", "birthday": "Thu Nov 01 2018 16:59:18 GMT-0300 (Hora oficial do Brasil)" }, "totalServices": 0, "user_detail": [] }
我不知道为什么,但是查询结果是一个 空数组 ,我试图使用$ unwind和$ match,但也无法正常工作。
编辑:
用户集合
{ "_id": "5b1b1dcce1ab9a12a8eb580f", "password": { "salt": "d095f2", "passwordHash": "b24881ef4c43d28e93bcff5da2ce32e4287aabf77540d2465482a435a5929a63f2ba9fb7e1cc14fa4e8183d83e33854ec6153fbbb872e65a9e3f188892bf56cc" }, "name": "Anderson Zanardi", "cpf": "31933765828", "phone": "11996370565", "birthday": "1984-03-18", "email": "dev@wabiz.com.br", "storeKey": "5b16cceb56a44e2f6cd0324b", "createdAt": "2018-06-09T00:22:36.464Z", "updatedAt": "2018-11-06T13:51:37.261Z", "__v": 0, "doc": "31933765828", "active": true, "from": "app", "deleted": false, "services": 80 },
时间表的收集
{ "_id": "5b1c20d8fc76f904849712c9", "customer": { "id": "789456", "name": "Gabriel Barreto", "phone": "11995274098", "cpf": "40735255814" }, "employee": { "id": "5b16cebd29bcf613f02b6fb4", "name": "Anderson Zanardi", "avatar": "" }, "service": { "filters": [ "corte_simples", "corte_masculino" ], "id": "service_id", "name": "Corte Masculino" }, "store": "5b16cceb56a44e2f6cd0324b", "scheduleDate": "2018-06-07", "scheduleStart": "2018-06-28 13:00", "scheduleEnd": "2018-06-28 13:30", "status": 1, "value": 50, "created": "2018-06-09T18:47:52.862Z", "updated": "2018-06-09T18:47:52.862Z", "__v": 0 },
猫鼬在创建时将集合名称复数。所以代替schedule你应该使用schedules
schedule
schedules
{ "$lookup": { "from": "schedules", "localField": "_id.id", "foreignField": "customer.id", "as": "user_detail" }}
或导入集合并从中提取集合名称
const Schedule = require('/schedules') { "$lookup": { "from": Schedule.collection.name, "localField": "_id.phone", "foreignField": "customer.phone", "as": "user_detail" }}