Join two or more queries in mongo db node.js and get result as a single object using aggregate query

user_12699233 0
Viewed 24

I have two collections as follows:

import mongoose from "mongoose";

const projectSchema = mongoose.Schema({
  id: String,
  userId: { type: mongoose.Schema.Types.ObjectId, ref: "User" },
  title: String,
  details: String,
  location: String,
  rate: String,

  status: {
    type: String,
    default: "active",
  },

  createdAt: {
    type: Date,
    default: new Date(),
  },
});

const Project = mongoose.model("Project", projectSchema);

export default Project;


import mongoose from "mongoose";

const proposalSchema = mongoose.Schema({
  id: String,
  userId: { type: mongoose.Schema.Types.ObjectId, ref: "User" },
  projectId: { type: mongoose.Schema.Types.ObjectId, ref: "Project" },
  rate: String,
  message: String,

  createdAt: {
    type: Date,
    default: new Date(),
  },
});

const Proposal = mongoose.model("Proposal", proposalSchema);

export default Proposal;

And in response to a GET request, I want to get all the projects which are active and user has not sent the proposal to them, GET request will have the id of user.

(Proposal: When a user sends a proposal, a proposal object is created in proposals collections which has userId and ProjectId)

I have make it work using the below queries but it doesn't looks efficient and good. Is there a way I can get this result using aggregate query or any better way from this?

And also how I can efficiently can convert objectId to string Id here.

export const getProjects = async (req, res) => {
  try {
    const activeProjects = await Project.find({ status: "active" }, { _id: 1 });

    const projectsWithProposals = await Proposal.find(
      {
        $and: [
          { userId: req.query.id },
          { projectId: { $in: activeProjects } },
        ],
      },
      { _id: 0, projectId: 1 }
    );

    const stringsIds = projectsWithProposals.map((id) =>
      id.projectId.toString()
    );

    const projects = await Project.find({
      $and: [{ status: "active" }, { _id: { $nin: stringsIds } }],
    });
    res.status(200).json(projects);
  } catch (error) {
    res.status(404).json({ message: error.message });
  }
};
mongoose mongodb-query node.js mongodb
1 Answers

Here is a aggregation function which delivers all Projects which have no proposal from a given user:

function getQ (userId) {
   return [
      {
         "$match": {
            "$expr": {
               "$eq": [
                  "$status",
                  "active"
               ]
            }
         }
      },
      {
         "$lookup": {
            "from": "proposals",
            "localField": "_id",
            "foreignField": "projectId",
            "as": "proposals"
         }
      },
      {
         "$set": {
            "uids": "$proposals.userId"
         }
      },
      {
         "$unset": "proposals"
      },
      {
         "$match": {
            "$expr": {
               "$not": [
                  {
                     "$in": [
                        mongoose.Types.ObjectId(userId),
                        "$uids"
                     ]
                  }
               ]
            }
         }
      },
      {
         "$unset": "uids"
      },
      {
         "$limit": 10
      }
   ]
}

db.Project.aggregate(getQ("62a61df204f2ce244ce0ffcc")) // input is user._id
  .then(console.log)
  .catch(console.error)

I have used the standard mongoose _ids so you might have to adapt the code if required.

The query does only output the Project collection data, although it would be easy to include other data as well.

Beware that limit is constant here. You could also convert skip and limit to function paramters which would make the function much more flexible if you are working with huge amounts of results.

user_17540760
Related