Crud: Any way to make the filter/or conditions case insensitive?

[chriszrc]

Seems like a common requirement, if it's not present already, do you think it would be hard to add?

[fwoelffel]

Doesn't this have something to do with you database collation? I believe SQL queries are case sensitive by default.

[chriszrc]

Right, sql queries are case sensitive by default, that's why I want a case insensitive way to do the filtering, so the api can respond to a request for "column=a" and "column=A" and do a query like "lower(column) = lower(:paramA)" automatically

[hakimio]

All the "LIKE" conditions should be case insensitive: starts, ends, cont, excl.

[fwoelffel]

All the "LIKE" conditions should be case insensitive: starts, ends,cont, excl.

This should be an option and not the default behavior. Otherwise it could break a lot of applications.

EDIT: I think this is what you meant, but I thought this ought to be clearly stated :wink:

[hakimio]

This should be an option and not the default behavior. Otherwise it could break a lot of applications.

Actually this seems to be different from one database to another. For example, MySQL LIKE is case-insensitive and in PostgreSQL it's case sensitive. And you use different methods depending on DB to change this behavior.

[fwoelffel]

You're almost right. From what I know that mainly depends on your collation, but I might be wrong. Sometimes, the collation has been chosen by the devs to have case sensitive searches, sometimes it's the opposite. Thus, @nestjsx/crud should never enforce such behavior by default.

Anyway, let's stick to the original subject.
@zMotivat0r would having a caseSentitive: boolean property in the FilterOptions be an acceptable option?

[zMotivat0r]

@fwoelffel yep, I think we can provide some option and adjust query interceptor.

[jayakusumah]

Will the feature be available? sometimes we need query like this.

where("LOWER(post.title) = LOWER(:title)", {title}) (for PostgreSQL)

[jakubszalaty]

Workaround (for PostgresSQL)
In app.module.ts add:

// @ts-ignore
TypeOrmCrudService.prototype.mapOperatorsToQuery = function(cond: QueryFilter, param: any): { str: string; params: ObjectLiteral } {
    const field = this.getFieldWithAlias(cond.field)
    let str: string
    let params: ObjectLiteral

    switch (cond.operator) {
        case 'eq':
            str = `${field} = :${param}`
            break

        case 'ne':
            str = `${field} != :${param}`
            break

        case 'gt':
            str = `${field} > :${param}`
            break

        case 'lt':
            str = `${field} < :${param}`
            break

        case 'gte':
            str = `${field} >= :${param}`
            break

        case 'lte':
            str = `${field} <= :${param}`
            break

        case 'starts':
            str = `${field} ILIKE :${param}`
            params = { [param]: `${cond.value}%` }
            break

        case 'ends':
            str = `${field} ILIKE :${param}`
            params = { [param]: `%${cond.value}` }
            break

        case 'cont':
            str = `${field} ILIKE :${param}`
            params = { [param]: `%${cond.value}%` }
            break

        case 'excl':
            str = `${field} NOT ILIKE :${param}`
            params = { [param]: `%${cond.value}%` }
            break

        case 'in':
            /* istanbul ignore if */
            if (!Array.isArray(cond.value) || !cond.value.length) {
                this.throwBadRequestException(`Invalid column '${cond.field}' value`)
            }
            str = `${field} IN (:...${param})`
            break

        case 'notin':
            /* istanbul ignore if */
            if (!Array.isArray(cond.value) || !cond.value.length) {
                this.throwBadRequestException(`Invalid column '${cond.field}' value`)
            }
            str = `${field} NOT IN (:...${param})`
            break

        case 'isnull':
            str = `${field} IS NULL`
            params = {}
            break

        case 'notnull':
            str = `${field} IS NOT NULL`
            params = {}
            break

        case 'between':
            /* istanbul ignore if */
            if (!Array.isArray(cond.value) || !cond.value.length || cond.value.length !== 2) {
                this.throwBadRequestException(`Invalid column '${cond.field}' value`)
            }
            str = `${field} BETWEEN :${param}0 AND :${param}1`
            params = {
                [`${param}0`]: cond.value[0],
                [`${param}1`]: cond.value[1],
            }
            break

        /* istanbul ignore next */
        default:
            str = `${field} = :${param}`
            break
    }

    if (typeof params === 'undefined') {
        params = { [param]: cond.value }
    }

    return { str, params }
}

It's based on https://github.com/nestjsx/crud/blob/master/packages/crud-typeorm/src/typeorm-crud.service.ts#L615

[sandeshgb]

@jakubszalaty really appretiate your workaround, but still I'm expecting a much better solution.
@fwoelffel @zMotivat0r as you have suggested any property to achieve to same?