Query and join referenced data

Question

Pretty new to mongo and I can't figure out what approach is best to do a join on my referenced data. I'm unsure if I should do two pulls.

I have a node application that runs mongoose. It's a collection of agenda items and each item has tasks.

Here is the info on the relevant schemas:

Agenda Schema:

var agendaSchema = new mongoose.Schema({
       type : {type: String, required: true},
       description : {type: String, required: true},
       status: { type: String, enum: ['New', 'Assigned', 'Closed'], required: true },
       followup: {type: Date},
       created: {type: Date, default: Date.now()},
       assigned : String,
       crq_nbr : Number,
       tasks : [
          {
             type: mongoose.Schema.Types.ObjectId,
             ref: "Task"
          }
       ]
});

Task Schema:

var taskSchema = mongoose.Schema({
   created : { type: Date, default: Date.now },
   completed : { type: Date, default: Date.now },
   status: {type: String, enum: ["Open","Closed"]},
   author: {
      id : { type: mongoose.Schema.Types.ObjectId, ref: "User" },
      name: String
   },
   text : String,
   updated : Date
});

I'm trying to pull all Agenda items that have task that is "completed" status.

One idea I had was to pull all agenda items, populate them based on a $match. After that I would filter out any agenda items that do not have a task in it using javascript.

Ex: (Still have to add the $date condition)

Agenda.find({}).populate({
    "path": "tasks",
    "match": {"status" : "Closed"}
}).exec(function(err, foundAgenda) {
    let newAgenda = foundAgenda.filter(function(agenda) {
        if (agenda.tasks.length > 0) return true;
    })
    res.send(newAgenda);
});

Another horrible idea I had was to find all tasks and find the ID's then findthe agenda based on those IDs and populate it. The problem with this is not only is it two seperate queries

Task.find({ "status": "Closed"}, { _id: 1 }, function(err, foundTasks) {
    if (err) {} else {
        var taskArray = [];
        foundTasks.forEach(function(task) {taskArray.push(task)});

        Agenda.find({"tasks" : {$in : foundTasks}}, function(err, foundAgenda) {
            res.send(foundAgenda);
        });
    }
});

Both of these might work for me but I'm sure there are better ways. What's the best approach for this type of join?

Normally you would use [`$lookup`](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) to pull tasks for each agenda and then filter tasks with completed status. — s7vr, Sep 28 '17 at 21:01
Possible duplicate of [Aggregation filter after $lookup](https://stackoverflow.com/questions/36459983/aggregation-filter-after-lookup) — s7vr, Sep 28 '17 at 21:01

Dan · Answer 1 · 2018-02-26T16:06:39.307

Maybe try this module https://www.npmjs.com/package/@coolgk/mongo

The query below will give you only the agenda rows that have tasks with "closed" status.

Agenda.find({}, {
    join: {
        on: 'tasks',
        filters: {
            'status' : 'Closed'
        }
    }
})

More examples:

Join

SQL to @coolgk/mongo

Left Join

SELECT * FROM a LEFT JOIN b ON a.b_id = b.id

becomes

model.find({}, {
    join: [ { on: 'b_id' } ]
})

Result:

[{
    _id: '5a8bde4ae2ead929f89f3c42',
    a_name: 'aname1',
    b_id: {
        _id: '5a8bde4ae2ead929f89f3c41',
        b_name: 'bname1'
    }
}, { ... }, ... ]

Inner Join with Constraints

SELECT * FROM a, b WHERE a.b_id = b.id AND b.b_name = 'bname1'

becomes

model.find({}, {
    join: [ { on: 'b_id', filters: { b_name: 'bname1' } } ]
})

Result:

[{
    _id: '5a8bdfb05d44ea2a08fa8a4c',
    a_name: 'aname2',
    b_id: {
        _id: '5a8bdfb05d44ea2a08fa8a4b',
        b_name: 'bname2'
    }
}]

Inner Join on Mulitple Collections

SELECT * FROM a, b, c WHERE a.b_id = b.id AND b.c_id = c.id AND c.c_name = 'cname3'

modela.find({}, {
    join: [{
        on: 'b_id',
        join: [{
            on: 'c_id',
            filters: { c_name: 'cname3' }
        }]
    }]
})

Result:

[{
    _id: '5a8bdfc1b07af22a12cb1f0b',
    a_name: 'aname3',
    b_id: {
        _id: '5a8bdfc1b07af22a12cb1f0a',
        b_name: 'bname3',
        c_id: {
            _id: '5a8bdfc1b07af22a12cb1f09',
            c_name: 'cname3'
        }
    }
}]

ps. I created this module.

Query and join referenced data

1 Answers1

Join