Mongoose query where X is in both arrays and where Y is in only one array

I am building a filtering system and I am stuck on a problem, please keep in mind that this is demo code, this is not the same project but the concept is the same, I replaced the names to make it as easily understandable as possible.

I got a filled filter object which gets sent like this (it consists of an array with id’s) – keep this one in mind:

const filters = {
  companies: ["company_a", "company_b"], // this is an example, normally it a mongodb ObjectId("XXXXX") list
  states: ["state_1", "state_2"], // this is an example, normally it a mongodb ObjectId("XXXXX") list
}

If you would take a look at both the Company and State collections, they would look something like this:

// MongoDB collection: `companies`
[
  {
    "id": "company_a",
    "nationwide": false
  },
  {
    "id": "company_b",
    "nationwide": true
  }
]
// MongoDB collection: `states`
[
  {
    "id": "state_1",
    "name": "Arizona"
  },
  {
    "id": "state_2",
    "name": "Texas"
  }
]

There is also a global collection which combines both of these, this is the collection I’m going to be using:

// MongoDB collection: `country_companies`
[
  /* record 1 */
  {
    "_id": ObjectId("XXXXX"),
    "company": {
      "_id": "company_a",
      "nationwide": false
    },
    "state": {
      "_id": "state_1",
      "name": "Arizona"
    }
  },
  /* record 2 */
  {
    "_id": ObjectId("XXXXX"),
    "company": {
      "_id": "company_b",
      "nationwide": true
    },
    "state": {
      "_id": "state_2",
      "name": "Texas"
    }
  }
]

---

Now, a company can be nationwide as well as state-oriented (as seen in above collection). So I have a repository like this:

export class CompanyRepository {
  private companies: Company[];

  public async initialize(): Promise<void> {
    if (this.companies.length > 0) throw new Error("Companies have already been initialized!");
    this.companies = await CompanyModel.find().exec();
  }

  public isCompanyNationwide(id: string): boolean {
    return this.companies.some(company => company.id === id && company.nationwide === true);
  }
}

---

The problem occurs that once I execute the query like this, with the filters at the top:

export class CompanyService {
  public static async getCompaniesByFilters(filters: CompanyFilters): Promise<Company[]> {
    const query: Record<string, unknown> = {};
    if (filters.companies.length > 0) query['company._id'] = { $in: filters.companies };
    if (filters.states.length > 0) query['state._id'] = { $in: filters.states };
    /* this results in a mongodb query:
      {
        "company._id": { $in: ["company_a", "company_b"] },
        "state._id": { $in: ["state_1", "state_2"] }  
      }
    */
    return await CountryCompanyModel.find(query).exec();
  }
}

What the above code basically does, is it adds the items based on if you selected them, in the end you get a query object. The problem there is that it has to be in BOTH arrays. So since "company_a" is nationwide, it shouldn’t be searched in the states array.

---

To get a clear view of the point, here are some examples of how the system should work:

User A selects `["company_a"]`, without any states ->
  Receives a list of all company_a records

User B selects `["company_a"]`, with the state `["state_1"]` ->
  Receives list of all company_a in state_1 records

User C selects `["company_a", "company_b"]` with the states `["state_1"]` ->
  Receives a list of all company_a in state_1, together with all company_b (since company B is nation-wide)

User D selects `["company_b"]` with the states `["state_1", "state_2"]` ->
  Receives a list of all company_b, because company_b is nation wide so states filter should be ignored entirely.

A solution I can think of is this:

import CompanyRepository from "./company.repository";

const stateWideCompanies = filters.companies.filter(companyId => 
  CompanyRepository.isCompanyNationWide(companyId) === false
);
const nationWideCompanies = filters.companies.filter(companyId => 
  CompanyRepository.isCompanyNationWide(companyId) === true
);

const countryCompaniesStates = await CountryCompanyModel.find({"company._id": { $in: stateWideCompanies }, "state._id": { $in: filters.states }).exec(); 
const countryCompaniesNation = await CountryCompanyModel.find({"company._id": { $in: nationWideCompanies }).exec();

const companyList = [...countryCompaniesStates, ...countryCompaniesNation]

This gives me what I want, however I think this should be able to be completed by the database. Because now I have to do two queries and combine them both, this does not look clean at all.

I hope that I can do this in ONE query to the database. So either the query builder should be fixed or the query itself, I can’t seem to get it working properly..

Answer

All you need to do is built a smarter query with boolean logic, In this case all you want to do is allow a nationwide company to be fetched regardless of the selected states.

Here’s how I would do it:

const query: Record<string, unknown> = {};

if (filters.companies.length > 0) {
    query['company._id'] =  { $in: filters.companies };   
}
if (filters.states.length > 0) {
    query['$or'] = [
            {'state._id': { $in: filters.states }},
            { 'company.nationwide': true}
        ];
}

Now if a state is selected the query is either the state._id is in the selected query OR the company is nationwide.