nodejs /Mongo - multiple finds not working

Question

Still a NooB to Node/Mongo and am stuck on this.

I have two mongo collections, Tenants and Rent. Rent collection has the tenant _id in the schema. The following function is searching through all active tenants and for each of those pulling out some attributes for the latest rent document. The first part of the function populates the tenant object with the results. all working good. The second .then starts to iterate through the tenant object pulling out the _id to use in the Rent query. (join). the issue is the for loop seems to iterate through and print the _id correctly, but the second find query seems to only print out the last document in the object. I am just not sure why this is happening

thanks in advance

app.get('/chargerenttest/:date', (req,res) => {

//check date is valid
var rentChargeDate = new Date(req.params.date);
var tenant = "";
//for each active tenant



 Tenant .find({
           activeTenant : true
            })
         .then ((tenant) => {          
          if (!tenant) {
             return res.status(404).send();
           }
           console.log("found tenents")
           return tenant
          })

          .then ((tenant) => {
           for (var i in tenant) {
                console.log(i)
                console.log(tenant[i]._id)

               Rent 
                .find({
                  "paymentType" :"Rent",
                  "tenantID" : tenant[i]._id,
                  "activeEntry": true})
                .limit(1)
                .sort({datePaid: -1})
                  // sort in decending date ( latested on top)
                .then ((rent) => {
                      lastPayment = rent[0].datePaid;
                      lastAmountPaid = rent[0].amountPaid;

                      console.log("--------",i)

                      console.log("tenant",tenant[i]._id)
                      console.log("rentamount",tenant[i].rentAmount)
                      console.log("lastpayment", lastPayment)           
                  });           
             }

           })

})

Answer 1

This module @coolgk/mongo could make joining multiple collections a lot simpler.

Examples

SQL to Mongo Join

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'

becomes

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'
        }
    }
}]

Answer 2

Your query can be simplified by running an aggregate operation that makes use of a pipeline with the $lookup operator which allows you to perform a left outer join to another collection in the same database to filter in documents from the "joined" collection for processing.

Consider running the following pipeline:

Rent.aggregate([
    {
        "$match": {
            "paymentType": "Rent",
            "activeEntry": true
        }
    },
    {
        "$lookup": {
            "from": "tenants",
            "localField": "tenantID",
            "foreignField": "_id",
            "as": "tenants"
        }
    },
    { "$match": { "tenants": { "$ne": [] }, "tenants.activeTenant": true } },
    //{ "$unwind": "$tenants" },
    { "$sort": { "datePaid": -1 } },
    { "$limit": 1 }     
]).exec((err, rent) => {
    if (err) throw err;

    lastPayment = rent[0].datePaid;
    lastAmountPaid = rent[0].amountPaid;
    tenant = rent[0].tenants[0];

    console.log("tenant",tenant._id)
    console.log("rentamount",tenant.rentAmount)
    console.log("lastpayment", lastPayment) 
});