RemultSql filter and Custom filter
Sometimes we need to use a custom/complex filter that is not yet in remult. We can specify that filter using SQL (mongo or knex). For example, let's say we want to filter and see only tasks whose title length is greater than 10. To do that with SQL, we can write the following code:
ts
console.table(
await remult.repo(Task).find({
where: SqlDatabase.rawFilter((whereFragment) => {
whereFragment.sql = 'length(title)>10'
})
})
)- Note that the
rawFiltermethod receives anasyncarrow function, this means that you can create complex logic here and maybe even other queries which their results will be used as part of thatrawFilter. This code will work great on the backend, but if we try to run it in the frontend, we'll get the 'database custom is not allowed with api calls.' error. To call it from the frontend, we need to use arawFilter
In the entity class, we'll add the titleLengthFilter static method
ts
@Entity<Task>("tasks", {
allowApiCrud: true
})
export class Task {
//...
static titleLengthFilter = Filter.createCustom<Task>(async () => {
return SqlDatabase.rawFilter((whereFragment) => {
whereFragment.sql = 'length(title)>10'
})
});
}rawFilter allows us to wrap server-side filters with an easy-to-use API and use it in the frontend. Here's how you can use it both in the frontend and also the backend
ts
console.table(
await remult.repo(Task).find({
where: Task.titleLengthFilter()
})
)::: Under the hood That custom filter translates to a rest request call that's translated on the backend, and looks like this:
http://127.0.0.1:3002/api/tasks?%24custom%24titleLengthFilter=%7B%7D:::
The rawFilter can also receive and use arguments:
ts
@Entity<Task>("tasks", {
allowApiCrud: true
})
export class Task {
//...
static titleLengthFilter = Filter.createCustom<Task, { minLength: number }>(
async ({ minLength }) => {
return SqlDatabase.rawFilter((whereFragment) => {
whereFragment.sql = 'length(title)>'
+ whereFragment.addParameterAndReturnSqlToken(minLength)
})
});
}- We use
addParameterAndReturnSqlTokento prevent sql inject, the sql that will be generated from this will look like this:sqlselect id, title, completed from tasks where length(title)>$1
Here's how we use that filter:
ts
console.table(
await remult.repo(Task).find({
where: Task.titleLengthFilter({
minLength: 10
})
})
)Other database types
We can use custom filter with other data providers:
knex
ts
static idBetween = Filter.createCustom<Task,{
from:number,
to:number
}>(
() => {
return KnexDataProvider.rawFilter(async ({from,to}) => {
return knexQueryBuilder => {
knexQueryBuilder.andWhereBetween('id', [from, to]);
}
})
});Json
ts
static titleLengthFilter = Filter.createCustom<Task, { minLength: number }>(
({ minLength }) => {
return ArrayEntityDataProvider.rawFilter( (item) => {
return item.title?.length>minLength
})
});
});