How to call oracle's CONTAINS function in BlazeJPAQuery subquery where clause?

Question

I'm trying to find entities by their collection elements properties. I also need to get score from oracle text index. Here is my query code.

    List<EntityWithNestedScore> fetch =
        new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory)
            .select(entityWithNestedScore)
            .from(entityWithNestedScore)
            .leftJoin(
                    select(entityWithNestedScoreAddress)
                    .from(entityWithNestedScore.addresses, entityWithNestedScoreAddress)
                    .where(Expressions.booleanTemplate("contains(TXT_INDEX, 'ndata(Street, Poznańska) accum ndata(City, Zamość) accum ndata(House_no, 13) accum ndata(Flat_no, 7)', 1)>=1")), entityWithNestedScoreAddress)
                    .lateral()
            .where(predicateForCheck)
            .fetch();

After running code i get exception: com.blazebit.persistence.parser.expression.SyntaxErrorException: No function or macro with the name 'contains' could not be found! Is there a way to execute CONTAINS function in where clause of subquery?

score 1 · Answer 1 · answered Apr 01 '21 at 08:02

The contains function is not registered and in JPA terms, you must use the generic function wrapper for this to work properly. I'm no QueryDSL expert, but try something like the following:

.where(Expressions.booleanTemplate("function('contains', {0}, 'ndata(Street, Poznańska) accum ndata(City, Zamość) accum ndata(House_no, 13) accum ndata(Flat_no, 7)', 1)>=1")), entityWithNestedScoreAddress.textIndex)

How to call oracle's CONTAINS function in BlazeJPAQuery subquery where clause?

1 Answers1