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]

这给了我我想要的，但我认为这应该能够由数据库完成。因为现在我必须执行两个查询并将它们组合起来，所以这看起来一点也不干净。

我希望我能在一个查询数据库中做到这一点。所以无论是查询生成器应该是固定的或查询本身，我似乎不能让它正常工作。

Answer 1

您所需要做的就是用布尔逻辑构建一个更智能的查询，在这种情况下，您所要做的就是允许获取全国性的公司，而不管所选择的州。

我是这样做的:

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在所选查询中，要么是该公司在全国范围内。