Nodejs mongoose Aggregate $lookup From Must be the database name

Define the model itemIn

// 1. Introduce mongoose
const mongoose = require('mongoose');
2. Instantiate the Schema
const Schema = mongoose.Schema;

// 3. Create Schema
const ItemInSchema = new Schema(
    {
        operator: {
            type: Schema.Types.ObjectId,
            ref: 'users' // Which table to associate with
        }, 
        total: {
            type: Number
            // min: 0
            // Required: [false, 'Please fill in the amount of income ']},... }, {timestamps: true});module.exports = ItemIn = mongoose.model('itemIn', ItemInSchema);

Copy the code

Define the model itemProject

// 1. Introduce mongoose
const mongoose = require('mongoose');
2. Instantiate the Schema
const Schema = mongoose.Schema;
// 3. Create Schema
const ItemProjectSchema = new Schema(
    {
        operator: {
            type: Schema.Types.ObjectId,
            ref: 'users' // Which table to associate with
        },
        // Item Id [or account Id (involving transfer)]
        itemOutInId: {
            type: Schema.Types.ObjectId,
            ref: 'itemOutIn'.// ref: 'itemOutIn', // to which table is associated
            required: [true.'Please fill in the associated income and expenditure item Id']},... }, {timestamps: true}); ItemProjectSchema.set('toObject', {virtuals: true});
ItemProjectSchema.set('toJSON', {virtuals: true});

module.exports = ItemProject = mongoose.model('itemProject', ItemProjectSchema);

Copy the code

Invalid because the FROM field of $lookup corresponds to the database name instead of the model name

Solutions:

Log in to the database and check the name of the actual stored database

# 1. Go to the bin directory of the database installation directory, type mongo and press Enter
PS D:\tools\mongodb\bin> ./mongo

# 2.show dbs
> show dbs
admin     0.000GB
config    0.000GB
help-app  0.002GB
local0.000 GB WXLT - API 0.000 GB# 3.use your-db-name
> use wxlt-api
# 4.show tables
> show tables
itemins
itemoutins
itemouts
itemprojects

Copy the code
 const result = await ModelItemProject.aggregate([
        {
            $lookup: {
                from: 'itemIn'.localField: 'projectId'.// field in the orders collection
                foreignField: '_id'.// field in the items collection
                as: 'itemins'}},]);Copy the code

Mongoose. Model (‘itemIn’, ItemInSchema) corresponds to a database named Itemins

Change from: ‘itemIn’ to ‘itemins’

The code is as follows:

 const result = await ModelItemProject.aggregate([
        {
            $lookup: {
                from: 'itemins'.// Database name
                localField: 'projectId'.// field in the orders collection
                foreignField: '_id'.// field in the items collection
                as: 'itemins'}}, {$lookup: {
                from: 'itemouts'.localField: 'projectId'.// field in the orders collection
                foreignField: '_id'.// field in the items collection
                as: 'itemouts'}}, {$match: queryParamsFileds
            // $match: {$or: [{type: '1'}, {type: '2'}]}
            $match: {type: {$in: ['1', '2']}} $match: {type: {$in: ['1', '2']}
        },
        {
            $unwind: {path: '$itemins'.preserveNullAndEmptyArrays: true}}, {$unwind: {path: '$itemouts'.preserveNullAndEmptyArrays: true}}, {$group: {
                _id: '$itemOutInId'.countMoney: {$sum: '$money'},
                count: {$sum: 1}}}, {$project: {
                _id: 0.itemOutInId: '$_id'.count: '$count'.value: '$countMoney'}}, {$lookup: {
                from: 'itemoutins'.localField: 'itemOutInId'.// field in the orders collection
                foreignField: '_id'.// field in the items collection
                as: 'itemOutIn'}}, {$unwind: {path: '$itemOutIn'.preserveNullAndEmptyArrays: true}}]);Copy the code