How to Implement Full-Text Search with PostgreSQL Using Blaze Persistence CriteriaBuilder?

I am designing a Dynamic Criteria Builder which takes in filter parameters (field, operator, value) and returns constructed CriteriaBuilder object. I iterate over parameters and use each parameter to add to Criteria, where:

• Field: used in .where().

• Operator: Determines the restriction (.eq(), .le(), .isNull(), etc.).

• Value: Added to the restriction as parameter, if applicable.

While most of the time it does the job, I'm having difficulties implementing Restriction Builder for Full text search of PostgreSQL (https://www.postgresql.org/docs/current/textsearch-intro.html#TEXTSEARCH-MATCHING), particularly with"@@" operator / predicate.

I assume that policy of Blaze Persistence developers might be similar to Criteria API developers (https://github.com/spring-projects/spring-data-r2dbc/issues/825), so I simply wanted to check if someone else tried solving this task using Blaze Persistence and came up with elegant solution (or at least a better one than I came up with).

Suggested solution:

My approach adds two custom JPQL functions: first invoked in the BaseWhereBuilder, the second one in the RestrictionBuilder:

First function "TS_VECTOR_PREFIX":

@Override
    public void render(FunctionRenderContext context) {
        context.addChunk("1");
    }

Second function "TS_VECTOR_MAIN":

@Override
    public void render(FunctionRenderContext context) {
        context.addChunk("1 AND TO_TSVECTOR('simple',");
        context.addArgument(0);
        context.addChunk(") @@ TO_TSQUERY('simple',");
        context.addArgument(1);
        context.addChunk(")");
    }

The idea is to "fool" the CriteriaBuilder by adding two conditions at once:

1. A trivial condition (1 = 1) to utilize added "="from .eqExpression() from the CriteriaBuilder.

2. The actual full-text search condition using @@.

Here is how it looks like in the CriteriaBuilder (I want to return entities which has words "pamel" and "Blake" in the singe_string_name column, adding prefix search):

public <T> PagedList<T> findRecords(Class<T> view, QueryData query, int offset, int pageSize, Sort sort) {
        CriteriaBuilder<SomeEntity> criteriaBuilder = criteriaBuilderFactory.create(entityManager, SomeEntity.class);

        criteriaBuilder.where("TS_VECTOR_PREFIX()").eqExpression("TS_VECTOR_MAIN(names.singleStringName, 'pamel:*&Blake:*')");

        return entityViewManager.applySetting(EntityViewSetting.create(view), criteriaBuilder)
                .page(offset, pageSize)
                .getResultList();
    }

While this solution works for my current needs, I find it inelegant due to the fact that I create main condition itself by passing it as a Value for RestrictionBuilder. I wonder if anyone else has solved this task more cleanly using Blaze Persistence or similar tools. Any suggestions or insights would be greatly appreciated!

Since Blaze-Persistence does not support boolean functions as predicates yet, this is the best that you can do unfortunately. You could try to render (TO_TSVECTOR('simple',?) @@ TO_TSQUERY('simple',?")) and use a boolean literal =true on the other side of the condition if you like that better, but I don't know how the PostgreSQL query planner likes that.