How to add dynamic Where queries in objection.js?

I have a table which has a global search bar that needs to trigger search for all fields like firstName, lastName, email and role.

Also have a dynamic filter which can have single or multiple filter like “firstName” or/And “lastName”

Obviously they need to be paginated.

For pagination i can use Model.query().page(1, 10)

But how to supply search or filter. assuming only search or filter active at a given time.

Search and filter both using LIKE. How to dynamically do this.

Answer

Objections allows you to modify the query:

let filtersArr = [.. build your filters]

await YourModel
.query()
.modify((queryBuilder) => {
    if (hasFilters) {
        filtersArr.forEach(({criteria, value}) => {
            queryBuilder.where(criteria, value)
        })
    }

    if (hasQ) {
        queryBuilder.where('name', 'ilike', `%${q}%`)
    }
  })
 .page(page, paginate)

this can be extended to include relations, or sort and it doesn’t have to be exclusive for one at the time, you can filter, sort and search all within the same query