mongoose filter based on related table

Question

I have developed a simple schema with 2 Tables Roles and Users. The users table references the role table.

I want to select all users which have a specific role.

Towards this I have written this code

models.js

var RoleSchema = new mongoose.Schema({
    rolename : {type: String, required: true}
});
var RoleModel = db.model('Role', RoleSchema, 'Role');

var UserSchema = new mongoose.Schema({
    username : {type: String, required: true},
    email : {type: String, required: true},
    role : {type: mongoose.Schema.Types.ObjectId, ref: 'Role', required: true}
});
var UserModel = db.model('User', UserSchema, 'User');
module.exports = {Role: RoleModel, User: UserModel};

app.js

var models = require('../models.js);

var rolename = req.params.rolename;
logger.info('came inside getUserByRole ' + rolename);
models.User 
.where('role.rolename').equals(rolename)    
.exec(function(err, users){
    if (err) res.json(501, 'Internal Error');
    if (users == null) res.json(404, 'No users found');
    res.json(200, users);
});

But this code returns all the users. my hope was that the user list will be filtered by the role name which I have specified.

I also tried

exports.getUsersByRole = function(req, res) {   
    var rolename = req.params.rolename;
    logger.info('came inside getUserByRole ' + rolename);
    models.User 
    .find({})
    .populate({
        path: 'role',
        match: {'role.rolename': rolename}
    })
    .exec(function(err, users){
        if (err) res.json(501, 'Internal Error');
        if (users == null) res.json(404, 'No users found');
        res.json(200, users);
    });
};

But this also returns all the users (and the role null). does not filter on rolename and does not populate the rolename.

score 3 · Accepted Answer · edited May 23 '17 at 10:32

3

Because the role is a reference, you'll need to populate as you've done in the second try. But this populate won't occur until the query is complete, so you'll need to filter after the initial query (similar to this answer).

So in your scenario, something like this:

models.User.find().populate("role").exec(function(err, users) {
    users = users.filter(function(user) {
        return user.role.rolename === req.params.rolename;
    });

    res.send(users);
});

edited May 23 '17 at 10:32

Community

1
1

answered Jun 08 '14 at 17:40

dylants

22,316
3
26
22

1

does this mean that mongo will return all the records and the filtering is being done in the code (in the memory of node)? In a relational world ... I could have filtered the record in the db itself rather than bring everything on the server and then filtering there. Please correct me if I am wrong... I am quite new to this. – Knows Not Much Jun 08 '14 at 18:10
2

Yes, doing this will load all users, then you filter out only those than contain this specific `rolename`. And you're right, this isn't very efficient. These problems come up when you try to isolate these things into separate collections, when in a non-relational database world this might not be the best approach. You might want to consider storing the `rolename` within the `UserSchema` which will make your query faster. Here's another stack overflow post that might be helpful in understanding this problem: http://stackoverflow.com/questions/19572854/mongodb-query-on-populated-fields – dylants Jun 09 '14 at 16:05

mongoose filter based on related table

models.js

app.js

1 Answers1