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..
Advertisement
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.