skip to Main Content

For example I have two models

const User = new Schema({
  name: { type: String },
});
const Message = new Schema({
  user: { type: ObjectId, ref: 'User' },
  message: { type: String },
});

How to find messages by the key "name" in the user?

It doesn’t work

exports.get = async (req, res) => {
  return Message.find({ "user.name": req.query.name })
    .populate('user', 'name')
    .then((data) => res.json(data));
}

I understand why "user.name" doesn’t work, but I don’t know how to solve it

3

Answers


  1. You would want to use $lookup -> unwind -> match:

    Unwind: convert user array to user object from look up stage

    exports.get = async (req, res) => {
      return Message.aggregate([
        {
          $lookup: {
            from: "users",
            localField: "user",
            foreignField: "_id",
            as: "user",
          },
        },
        {
          $unwind: {
            path: "$user",
          },
        },
        {
          $match: {
            "user.name": req.query.name,
          },
        },
      ])
      .then((data) => res.json(data));
    }
    
    Login or Signup to reply.
  2. There is no concept of a join in Mongodb. Hence I would recommend using straight schema definition like this:

    const User = new Schema({
      name: { type: String },
    });
    
    const Message = new Schema({
      user: [User],
      message: { type: String },
    });
    

    then you wouldn’t need to use populate anymore. Instead, you would have something like this:

    Message.find({ "user.name": req.query.name })
    

    However, If you still prefer to use the current approach, You can try this:

    Message.find()
      .populate({
        path: "user",
        match: {
          name: req.query.name
        }
      })
    ...
    
    Login or Signup to reply.
  3. Basically you need the correct ordering of table
    User must be your primary table and Messages must be your secondary table from which you should lookup.
    The query is as following;

    exports.get = async (req, res) => {
    const queryLength = req.query.name ? req.query.name.length : 0;
    return User.aggregate([
        {
            $match: {
                name: {
                    "$regex": req.query.name,
                    "$options": '-i'
                }
            },
            $lookup: {
                let: { id: _id },
                from: 'Messages',
                pipeline: [
                    {
                        $match: {
                            $expr: { $eq: ["$user", "$$id"] }
                        }
                    },
                    {
                        $project: {
                            user: 0,
                            _id: 0,
    
                        }
                    }
                ],
                as: "Messages"
            },
            $project: {
                regex_match: {
                    $eq: [
                        req.query.name,
                        { $substr: [{ $toLower: "$name" }, 0, queryLength] }
                    ]
                },
                Messages: 1
            },
            $sort: {
                regex_match: -1//we use regex match to get the most match result on the top like if u search "Al" then all results with al in them will be on top
            },
            $project: {
                Messages: 1,
                regex_match: 0
            }
        }
    
    ])
        .then((data) => res.json(data));
    

    }

    This will return all the messages as an array with field name messages of "name" person e.g;

    {
       Messages: [{_id: 1234, message: "Hello"},{_id: 12345, message: "Hi"} ]
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search