Custom operators to implement an unaccented name-search feature

[linusdm]

Is there a way to define your own operator, or adapt existing operators somehow? I'd like to implement an unaccented substring search (to get an idea, it's explained in this SO answer). This requires a where clause like this:

SELECT * FROM entities WHERE unaccent(field) ILIKE unaccent('%param%');

To stretch the idea even further: I'd like this to work on compound fields, in a similar manner as the :ilike_and operator does now. So it's possible to search for example in a full name, where the full name is actually composed of a first and a last name. The search string should be broken apart, as described in the :ilike_and operator documentation.

The only way to play nice with Flop I see now is to define a custom field with a custom filter. I'd be grateful for any suggestion in case I overlooked some of the capabilities of Flop to implement the feature. I'm basically trying to build a robust name-search filter, taking into account the fact that accents are often difficult to get right, and that names can consist of different parts, which is also difficult to spell out correctly.

Thanks for taking the time, much appreciated!

[woylie]

Currently, custom fields are the only way to implement this.

There was an idea about derived fields before, but that's tricky to implement. You can find some thoughts on this here and here. In the proposal, you would only be able to supply the fragment for the field, while the operator and value would be set by Flop.

If you also need to convert the value in some way, either in the DB or in the application, that wouldn't suffice, though. For that, you'd need to extend the operators indeed. That wasn't on the roadmap so far, but I'm open for proposals.