how to connect 3 table in mongodb and node js

Question

in my case, I've 3 tables

1. users table

   name: {
       type: String,
       required: [true, 'Email is required'],
       trim: true,
       lowercase: true,
   },
   role: {
       type: String,
       required: true,
       enum: ['SUPER_ADMIN', 'ROOT_STANDERD', 'COMPANY_ADMIN', 'RETAILER_ADMIN']
   },
   company: {
       type: ObjectId,
       ref: 'company',
   },
   retailer: {
       type: ObjectId,
       ref: 'retailer'
   }
   });
   

2. Company Table

    const CompanySchema = new Schema({
    _id: String,
    name: {
        type: String,
        required: true,
        maxLength: 50,
    }})
   

3. Retailer Table

   const CompanySchema = new Schema({
      _id: String,
      name: {
        type: String,
        required: true,
        maxLength: 50,
    },
     company: {
       type: ObjectId,
       ref: 'company',
   }})
   

Let me explain how it works: SUPER_ADMIN can create the company, retailer(it will reference by Company), and user (if the user role is COMPANY_ADMIN then the user is referenced by company, and if the user role is RETAILER_ADMIN then the user is the reference by retailer)

SUPER_ADMIN can view all the users

Now my query is COMPANY_ADMIN can view their related company's user and retailer's user only.

Answer 1

In this case you can try customize query, you can try this

user.find({ $or: [{ retailer: { $in: [retailer_id1, retailer_id2...] } }, { company: user.company }] })