时间戳

首页 > 解决方案 > 如何查询以获取基于一个字段作为用户名引用到另一个集合的所有文档

mongodb - 如何查询以获取基于一个字段作为用户名引用到另一个集合的所有文档

问题描述

在我的 Node API 和 MongoDB 中，我试图创建一个端点来获取与配置文件的一个用户名关联的所有帖子。在我的配置文件架构中，我引用了发布架构，在我的发布架构中，我引用了配置文件架构中的用户名。我的问题我不知道如何获取该用户名的所有帖子。我做了类似的事情，但嵌入了体验模式，但我不确定如何为引用的集合执行此操作。

岗位型号：

const { Connect } = require("../db");

const reactionSchema = {
    likedBy: {
        type: String,
        unique: true,
        sparse: true
    }
};

const postSchema = {
    text: {
        type: String,
        required: true,
        unique: true,
        sparse: false
    },    
    username: {
        type: Connect.Schema.Types.String,
        ref: "Profile"
    },    
    image: {
        type: String,
        default: "https://via.placeholder.com/150",
        required: false
    },
    createdAt: {
        type: Date,
        default: Date.now,
        required: false
    },    
    updatedAt: {
        type: Date,
        default: Date.now,
        required: false
    },    
    reactions: [reactionSchema],    
    comments: {
        type: Connect.Schema.Types.ObjectId,
        ref: "Comment",
        required: false
    }
};

const collectionName = "post";
const postSchemaModel = Connect.Schema(postSchema);
const Post = Connect.model(collectionName, postSchemaModel);

module.exports = Post;

型材型号：

// Here defining profile model
// Embedded we have the Experience as []
const { Connect } = require("../db");
const { isEmail } = require("validator");

const postSchema = {
    type: Connect.Schema.Types.ObjectId,
    ref: "Post"
};

const experienceSchema = {
    role: {
        type: String,
        required: true
    },
    company: {
        type: String,
        required: true
    },
    startDate: {
        type: Date,
        required: true
    },
    endDate: {
        type: Date,
        required: false
    },
    description: {
        type: String,
        required: false
    },    
    area: {
        type: String,
        required: true
    },
    createdAt: {
        type: Date,
        default: Date.now,
        required: false
    },    
    updatedAt: {
        type: Date,
        default: Date.now,
        required: false
    },    
    username: {
        type: String,
        required: false
    },
    image: {
        type: String,
        required: false,
        default: "https://via.placeholder.com/150"
    }
};

const profileSchema = {
    firstname: {
        type: String,
        required: true
    },   
    surname: {
        type: String,
        required: true
    },    
    email: {
        type: String,
        trim: true,
        lowercase: true,
        unique: true,
        required: [true, "Email is required"],
        validate: {
            validator: string => isEmail(string),
            message: "Provided email is invalid"
        }
    },    
    bio: {
        type: String,
        required: true
    },    
    title: {
        type: String,
        required: true
    },    
    area: {
        type: String,
        required: true
    },    
    imageUrl: {
        type: String,
        required: false,
        default: "https://via.placeholder.com/150"
    },    
    username: {
        type: String,
        required: true,
        unique: true
    },    
    experience: [experienceSchema],
    posts: [postSchema],    
    createdAt: {
        type: Date,
        default: Date.now,
        required: false
    },    
    updatedAt: {
        type: Date,
        default: Date.now,
        required: false
    }
};

const collectionName = "profile";
const profileSchemaModel = Connect.Schema(profileSchema);
const Profile = Connect.model(collectionName, profileSchemaModel);

module.exports = Profile;

我能够为嵌入式体验做到这一点，但不确定是否适用于引用的集合：

const profileWithExperiences = await Student.aggregate([
  { $match: { username: res.username.username } },
  {
    $unwind: "$experience"
  },
  {
    $match: {
      "experience._id": new ObjectID(req.params.experienceId)
    }
  },
  {
    $project: {
      username: 1,
      experience: 1,
      _id: 0
    }
  }
]);

我想看一个引用集合的例子，因为这让我很困惑我应该怎么做

[编辑] 配置文件和帖子的 JSON

{
    "_id": ObjectId("5e2c98fc3d785252ce5b5693"),
    "imageUrl": "https://i.pravatar.cc/300",
    "firstname": "Jakos",
    "surname": "Lemi",
    "email": "lemi@email.com",
    "bio": "My bio bio",
    "title": "Senior IT developer",
    "area": "Copenhagen",
    "username": "Jakos",
    "experience": [
        {
            "image": "https://via.placeholder.com/150",
            "_id": ObjectId("5e3975f95fbeec9095ff3d2f"),
            "role": "Developer",
            "company": "Google",
            "startDate": ISODate("2018-11-09T23:00:00.000Z"),
            "endDate": ISODate("2019-01-05T23:00:00.000Z"),
            "area": "Copenhagen",
            "createdAt": ISODate("2020-02-04T13:47:37.167Z"),
            "updatedAt": ISODate("2020-02-04T13:47:37.167Z")
        },
        {
            "image": "https://via.placeholder.com/150",
            "_id": ObjectId("5e3978bf5e399698e20c56d4"),
            "role": "Developer",
            "company": "IBM",
            "startDate": ISODate("2018-11-09T23:00:00.000Z"),
            "endDate": ISODate("2019-01-05T23:00:00.000Z"),
            "area": "Copenhagen",
            "createdAt": ISODate("2020-02-04T13:59:27.412Z"),
            "updatedAt": ISODate("2020-02-04T13:59:27.412Z")
        }
    ],
    "createdAt": ISODate("2020-01-25T19:37:32.727Z"),
    "updatedAt": ISODate("2020-02-04T23:14:37.122Z"),
    "__v": NumberInt("0")
}

邮政

{
    "_id": ObjectId("5e3beb639e072afedd19dcef"),
    "username": ObjectId("5e2c98fc3d785252ce5b5693"),
    "image": "https://via.placeholder.com/150",
    "text": "My awesome post",
    "createdAt": ISODate("2020-02-06T10:33:07.22Z"),
    "updatedAt": ISODate("2020-02-06T10:33:07.22Z"),
    "reactions": [ ],
    "__v": NumberInt("0")
}

预期输出：

{
    "username": "Jakos",
    "postsCount": [1],
    "posts": [
        {
        "_id": ObjectId("5e3beb639e072afedd19dcef"),
        "image": "https://via.placeholder.com/150",
        "text": "My awesome post",
        "createdAt": ISODate("2020-02-06T10:33:07.22Z"),
        "updatedAt": ISODate("2020-02-06T10:33:07.22Z"),
        "reactions": [ ],
        "__v": NumberInt("0")
    }
    ]

}

我想查看与该用户名相关的所有帖子

标签： mongodbmongodb-queryaggregation-framework

解决方案

您可以使用$lookup聚合来加入集合。

db.profiles.aggregate([
  {
    $match: {
      username: "Jakos"
    }
  },
  {
    $lookup: {
      from: "posts",   //the name of the posts collection, change this if it is different
      localField: "_id",
      foreignField: "username",
      as: "posts"
    }
  },
  {
    $project: {
      username: 1,
      posts: 1,
      postsCount: {
        $size: "$posts"
      },
      _id: 0
    }
  }
])

操场

因为mongoose它在您的应用程序中应该是这样的：

const profileWithExperiences = await Student.aggregate([
  { $match: { username: res.username.username } },
  {
    $unwind: "$experience"
  },
  {
    $lookup: {
      from: "posts", //the name of the posts collection, change this if it is different
      localField: "_id",
      foreignField: "username",
      as: "posts"
    }
  },
  {
    $project: {
      username: 1,
      posts: 1,
      postsCount: {
        $size: "$posts"
      },
      _id: 0
    }
  }
]);

推荐阅读