CODEWITHSUNDEEP
node.jsmongodbmongoose
Mehdi M
Mehdi M

Reputation: 70

mongoose looking for a better way to get documents

Consider there are 3 collections:

Company: {
    name
}

Department: {
    name,
    company_id
}

Person: {
    name,
    department_id
}

and we want to get all persons in a company, by company name

I know the way to first get the company ID in one query, then get the list of department_ids in another query, and then get the list of persons, in another query with the list of Ids:

async function query(){
   let companyId = await Company.findOne({name}).distinct('_id').exec();
   let departmentIds = await Department.find({company_id : companyId } ).distinct('_id').exec();
   let persons = await Person.find({department_id : departmentIds}).exec();
}

I want to know if is there any better way to do this, I mean is it possible to use the populate method or aggregation here? and if yes how? Thanks in advance.

Upvotes: 2

Views: 67

Answers (2)

Onur Doğan
Onur Doğan

Reputation: 2058

You may try to use aggregate from the Company document. Then one more aggregation with lookup to reach the Company document.

After reaching the Company Document, you can easily match the name value:

return await Company.aggregate([
  {
    $lookup: {
      from: "departments",
      localField: "_id",
      foreignField: "company_id",
      as: "PersonCompanyTable",
    }
  },
  {
    $unwind: '$PersonCompanyTable'
  },
  {
    $lookup: {
      from: 'persons',
      localField: 'PersonCompanyTable._id',
      foreignField: 'department_id',
      as: 'PersonsTable'
    }
  },
  {
    $match: {
      name: name
    }
  }
])

I hope, it is useful for you.

Upvotes: 1

rickhg12hs
rickhg12hs

Reputation: 11902

There are many ways this could be done. Here's one way to get all persons working for every company using a "$lookup" nested inside another "$lookup".

db.Company.aggregate([
  { // get all company departments
    "$lookup": {
      "from": "Department",
      "localField": "_id",
      "foreignField": "company_id",
      "as": "departments",
      "pipeline": [
        { // get all persons in department
          "$lookup": {
            "from": "Person",
            "localField": "_id",
            "foreignField": "department_id",
            "as": "persons"
          }
        }
      ]
    }
  },
  {
    "$project": {
      "_id": 0,
      "name": 1,
      "persons": {
        // concat all department persons
        "$reduce": {
          "input": "$departments",
          "initialValue": [],
          "in": {
            "$concatArrays": ["$$value", "$$this.persons"]
          }
        }
      }
    }
  }
])

Try it on mongoplayground.net.

Upvotes: 1

Related Questions