Optimizing the Search Behaviour on DataGridQueryFilter

[kausik10]

I need to optimize the DataGridQueryFilter to include following conditions

1. Remove whitespaces
2. Remove the dots
3. Allow lowecase search

Current setup from contember allows me to perform lowecase search to obtain result. However, I am unable to remove the whitespaces and dots when searching.

text.ts

import { createGenericTextCellFilterCondition } from './common'
import { createFieldFilterHandler } from './createFilterHandler'

export type TextFilterArtifactsMatchMode = 'matches' | 'matchesExactly' | 'startsWith' | 'endsWith' | 'doesNotMatch'

export type TextFilterArtifacts = {
	mode?: TextFilterArtifactsMatchMode
	query?: string
	nullCondition?: boolean
}


export const createTextFilter = createFieldFilterHandler<TextFilterArtifacts>({
	createCondition: filter => {
		const condition = filter.query !== '' ? createGenericTextCellFilterCondition(filter) : {}

		if (filter.nullCondition === true) {
			return {
				or: [condition, { isNull: true }],
			}
		} else if (filter.nullCondition === false) {
			return {
				and: [condition, { isNull: false }],
			}
		}

		return condition
	},
	isEmpty: filter => {
		return !filter.query && filter.nullCondition === undefined
	},
})

`common.ts`

```js
import { EntityId } from '@contember/react-binding'
import { Input } from '@contember/client'

export type RelationFilterArtifacts = {
	id?: EntityId[]
	notId?: EntityId[]
	nullCondition?: boolean
}


export type GenericTextCellFilterArtifacts = {
	mode?: 'matches' | 'matchesExactly' | 'startsWith' | 'endsWith' | 'doesNotMatch'
	query?: string
}

export const createGenericTextCellFilterCondition = (filter: GenericTextCellFilterArtifacts) => {
	if (!filter.query) {
		return {}
	}
	const baseOperators = {
		matches: 'containsCI',
		doesNotMatch: 'containsCI',
		startsWith: 'startsWithCI',
		endsWith: 'endsWithCI',
		matchesExactly: 'eq',
	}

	let condition: Input.Condition<string> = {
		[baseOperators[filter.mode ?? 'matches']]: filter.query,
	}

	if (filter.mode === 'doesNotMatch') {
		condition = { not: condition }
	}
	return condition
}

I can see that we have or eq and some other sort of graphql filtes (I assume). And there is no provision in the baseOperators to get what I want.

I tried creating a function to generate string as I intended in the common.ts and used it like:

let condition: Input.Condition<string> = {
		[baseOperators[filter.mode ?? 'matches']]: custom_function(filter.query),
	}

However it didn't work as database still has the original text. Is there a way to implement such functionality with DataGridQueryFilter ? Thank you!

[jonasnobile]

You’re on the right track trying to transform the query!

However, the problem is that Contember’s DataGridQueryFilter only builds conditions for the database – it doesn’t modify the actual column values at query time. So if the database contains values with spaces and dots, they will still be matched literally unless you normalize them.

The best solution is to normalize the text at the database level using a view. You can learn more about views here: https://docs.contember.com/reference/engine/schema/views/

Here’s an example that:

• trims the text
• replaces dots and spaces
• makes everything lowercase

@c.View(`
    SELECT
        article.id AS id,
        article.id AS article_id,
        LOWER(
            REPLACE(
                REPLACE(
                    TRIM(COALESCE(article.title, '') || ' ' || COALESCE(article.plain_subtitle, '')),
                    ' ', ''
                ),
                '.', ''
            )
        ) AS normalized_search_field
    FROM article
`)
export class ArticleMetadata {
	article = c.oneHasOne(Article, 'meta')

	normalizedSearchField = c.stringColumn()
}

In this example:

• TRIM removes extra spaces at the ends
• REPLACE(..., ' ', '') removes all spaces inside
• REPLACE(..., '.', '') removes dots
• LOWER makes it lowercase

Then you can build your filter on normalizedSearchField, and your users can search without worrying about spaces, dots, or capitalization!

[kausik10]

Hi, thank you for the response. I will try and implement this solution in a few days.
I'll let you know via this discussion if I run into any problems.

[kausik10]

Hi, I implemented this solution and I got the result as I expected. Thank you!
However, I have a rather unexpected glitch which I want to address.

I had used DataGridQueryFilter initially.
However, I used DataGridTextFilter with field="normalizedView.normalizedComapnyName"
Doing this, I experienced a glitch as:

1. Type something in the search with DataGridQueryFilter
2. Switch to use the DataGridTextFilter, don't remove the search prompt in the UI.
3. The UI showed the results from DataGridQueryFilter and whatever you type in searchbar is irrelevant. It will still show the results from DataGridQueryFilter.

To remove this glitch, I had to logout of the application and login again. After that DataGridTextFilter worked as expected.
Is the component supposed to behave this way? Thank you!

[jonasnobile]

Thanks for your feedback and for describing the glitch.

To clarify how the filtering works:

• DataGridQueryFilter is designed to apply a global search across multiple scalar fields.
• DataGridTextFilter applies to a specific field—in your case, normalizedView.normalizedComapnyName.

These two filters can be used together, and when they are, the resulting query includes both filters, combined with an AND. For example:

{
  "and": [
    { "or": [{ "title": { "containsCI": "Hello" } }] },
    { "title": { "containsCI": "#15" } }
  ]
}

So if you typed something using DataGridQueryFilter and then switched to DataGridTextFilter without clearing the first input, the global filter was still active in the background. That’s why you saw the old results—your new input was being filtered in combination with the previous one.

Logging out likely cleared the persisted filter state, which is why the new filter worked afterward.