I am currently constructing a filtering mechanism and have run into a roadblock. It's worth noting that this code is for demonstration purposes only and does not belong to the actual project, but the core idea remains consistent. I have altered the names to ensure clarity.
I have a populated filter object sent in this format (comprising an array of IDs) - keep this structure in mind:
const filters = {
companies: ["company_a", "company_b"], // example, typically it would be a list of mongodb ObjectId("XXXXX")
states: ["state_1", "state_2"], // example, normally it would be a list of mongodb ObjectId("XXXXX")
}
If we inspect both the Company and State collections, they would resemble 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"
}
]
Additionally, there is a combined global collection which includes elements from both of these collections, and this is the collection that will be utilized:
// 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"
}
}
]
A company can either operate on a nationwide or state-specific basis (as observed in the aforementioned collection). Hence, I have designed a repository like so:
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 issue arises when I execute the query with the specified filters at the beginning:
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 };
/* generates a mongodb query:
{
"company._id": { $in: ["company_a", "company_b"] },
"state._id": { $in: ["state_1", "state_2"] }
}
*/
return await CountryCompanyModel.find(query).exec();
}
}
The above code essentially adds items based on user selections, culminating in a query object. The challenge lies in requiring inclusion in BOTH arrays. For instance, since "company_a" is nationwide, it should not be included in the states array search.
To clarify how the system should function, here are some examples:
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"]` ->
Gets a list of all company_a records in state_1
User C selects `["company_a", "company_b"]` with the states `["state_1"]` ->
Obtains a list of all company_a records in state_1, alongside all company_b records (since company B is nation-wide)
User D chooses `["company_b"]` with the states `["state_1", "state_2"]` ->
Gets a list of all company_b records, as company_b is nationwide and the state filter should be disregarded.
A possible solution could be this approach:
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 implementation achieves the desired outcome, yet I believe it should be feasible to accomplish within a single database query. Performing two queries and combining them does not seem efficient.
I aspire to conduct this task in ONE database query. Consequently, either the query builder needs tweaking or the query itself must be adjusted, as I'm unable to achieve the desired functionality.