How can i Join in mongodb and covert this SQL code to mongoose-CodePudding

select*
from AvailableProperty as a JOIN Branch as b 
where a.branchId=b._id

I tried this but it's not working properly.

 let data = await AvailableProperty.find({ Type_property: "Sell" })
    .populate({
      path: "branchId",
      match: { _id: req.user.id},
      select: "name _id adressNumber1 adressNumber2 status",
    })

CodePudding user response：

I think you are missing the reference in your Schema. If you add the reference information from what you are passing to the populate function in the Schema and then populate is suspect it would work!

For reference: https://mongoosejs.com/docs/populate.html

CodePudding user response：

To perform a join in Mongoose, you can use the populate() method on a query. This method allows you to reference documents in other collections and populate them in the results.

Here is an example of how you can use populate() to achieve the same result as the SQL query you provided:

  // Assuming you have created models for AvailableProperty and Branch
  const AvailableProperty = mongoose.model('AvailableProperty', availablePropertySchema);
  const Branch = mongoose.model('Branch', branchSchema);
  
  // Create the query
  const query = AvailableProperty.find().populate('branchId');
  
  // Execute the query and populate the results
  query.exec((err, properties) => {
    if (err) {
      // Handle the error
    } else {
      // properties will be an array of AvailableProperty documents with 
      // corresponding documents populated in the `branchId` field
    }
  });

In this example, the AvailableProperty model has a field named branchId that references the _id field in the Branch model. When the query is executed, the populate() method will replace the branchId field in each AvailableProperty document with the corresponding Branch document.

You can make that branchId field a reference to the _id in Branch model like this:

// Import the mongoose module and Schema object
const mongoose = require('mongoose');
const Schema = mongoose.Schema;

// Create a new AvailableProperty schema
const availablePropertySchema = new Schema({
  // Other fields here...

  // Define the branchId field
  branchId: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Branch'
  }
});

// Create the AvailableProperty model
const AvailableProperty = mongoose.model('AvailableProperty', availablePropertySchema);

I hope this helps. Let me know if you have any other questions.