Implement text search with ILIKE ALL - but all is deprecated?

[funsim]

I would like to implement a basic text search SQL query with ILIKE ALL (see example below).

CREATE TABLE users (
    name VARCHAR(255) NOT NULL
);
INSERT INTO users (name) VALUES ('Jim Knopf');

SELECT * FROM users WHERE name ILIKE ALL (array['%jim%', '%knopf%']);

This is possible with the diesel DSL thanks to the ilike and the all expressions:

users::table.query.filter(users::name.ilike(all(sql_search_query)))

The problem is that all was deprecated since version 2.0.0. The documentation suggests to use ne_all instead, which is not the right fit for the use-case above.

[weiznich]

That's essentially a duplicate of #3360. The short version is that nobody cared enough for this functionality to contribute a fix.

[funsim]

Good catch! What is the reason not to un-deprecate the any and all again? If that is no viable option, would the alternative be to implement "like_any", "like_all", "ilike_any", ... ?

[weiznich]

What is the reason not to un-deprecate the any and all again?

That's not desirable as the old DSL node could be used in locations where it is syntactically incorrect. That could lead to broken queries.

If that is no viable option, would the alternative be to implement "like_any", "like_all", "ilike_any", ... ?

That's the likely solution here, but before implementing such functions I would like to see a list of affected operators. If that's just these 4 functions that is likely the right solution, if there are significant more functions we might look for a different approach.

[funsim]

For reference, this issue points out the invalid queries that might be generated with any.