javascript - Mongoose 查询其中 X 在两个数组中并且 Y 仅在一个数组中

Question

我正在构建一个过滤系统，但遇到了一个问题，请记住这是演示代码，这不是同一个项目，但概念是相同的，我替换了名称以使其尽可能易于理解。
我得到了一个填充的过滤器对象，它像这样发送(它由一个带有 id 的数组组成) - 请记住这一点:

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
}

如果你想看看 Company和 State集合，它们看起来像这样:

// 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"
  }
]

还有一个结合了这两者的全局集合，这是我将要使用的集合:

// 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"
    }
  }
]

现在，一家公司可以是全国性的，也可以是面向国家的(如上图所示)。所以我有一个这样的存储库:

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);
  }
}

一旦我执行这样的查询，就会出现问题，过滤器位于顶部:

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();
  }
}

上面的代码基本上做了什么，它是根据你是否选择它们来添加项目，最后你会得到一个查询对象。问题在于它必须在两个数组中。所以自从 "company_a"是全国性的，不应该在 states 数组中搜索。

为了清楚地了解这一点，这里有一些系统应该如何工作的例子:

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.

我能想到的一个解决方案是:

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]

这给了我想要的东西，但是我认为这应该可以由数据库完成。因为现在我必须做两个查询并将它们结合起来，这看起来一点也不干净。
我希望我可以在对数据库的 ONE 查询中做到这一点。因此，无论是查询构建器还是查询本身，我似乎都无法使其正常工作..

Answer 1

您需要做的就是使用 boolean logic 构建更智能的查询。 , 在这种情况下，您要做的就是允许获取一个全国性的公司，而不管所选的州如何。
这是我的做法:

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

现在，如果选择了一个状态，则查询是 state._id在选定的查询中或公司在全国范围内。