I'm using knex.js, a SQL query builder. This question is not specific about knex.js though but about javascript semantics, so knex.js is just here to illustrate my problem. So I build SQL queries with knex.js by just chaining methods.
For instance, to get contacts in a table:
const getContacts = await knex
.select('*')
.from('contacts')
.where({age: 21}) // Filters contacts that have age 21
.andWhere({name: 'Bob'}) // Filters contacts that are named Bob
.andWhere({eyeColor: 'blue'}) // ...you got the idea
...
I want to make a function that given an array of filters, returns the built query to get the right set of contacts. I can theoretically put as many .andWhere that I want so I'd like do declare that with a map over the input array or something like that, i.e
const getContactsQuery = (filterArray) => (
knex
.select('*')
.from('contacts')
.where(filterArray[0])
.andWhere(filterArray[1])
...
.andWhere(filterArray[filterArray.length - 1])
)
The question: Is there any way I can build this chain of .andWhere mapping over filterArray?
const getContactsQuery = (filterArray) => (
filterArray.reduce((acc, filterElement, index) => {
if(index === 0) {
return acc.where(filterElement)
}
return acc.andWhere(filterElement)
},
knex.select('*').from('contacts')
)
)
This may not work because this would get knex to fetch the table elements at each iteration of the reduce.
EDIT: It should actually work for knex because "knex does not query the database unless you await the last chaining". So this is solved for my context but there eventually could be other use cases where this would not work, don't know 🤷♂️
Is this something that would work for you?
const getContactsQuery = async (filterArray) => {
const query = knex
.select('*')
.from('contacts');
filterArray.forEach((filter,i) => {
if(i==0)
query.where(filter)
else
query.andWhere(filter)
})
return await query
}
For the semantics of when a knex query is actually executed, there is a good answer here: https://stackoverflow.com/a/57758854/3134549
Of course if you don't want the query to be executed at the end of the function call, mark it as non-async and return the unawaited query.
If all of the filters requires an AND boolean operator, you can pass an object with multiple key->value, and knex will construct the query with AND.
For example:
const getContactsQuery = (filterObj) => {
return knex.select('*')
.from('contacts')
.where(filterObj);
};
const results = await getContactsQuery({
col1: 'val1',
col2: 'val2',
col3: 'val3',
});