Using Criteria to see if array contains 1 or more elements

[GeitV]

We've been using criteria for filtering endpoints to create easily readable DB fetches based on what users filter by. But there's one field that we are having trouble with.

In Postgres DB, we have a field, lets call it services, that is text[] type. Now, users would like to tick boxes (select 1 or more services) to see records. In plain SQL this would be where services && array['selection1', 'selection2']
How to do it with Criteria?

We already have a lot of filtering done in Criteria, so I'd like to not go back to plain SQL because one of filter parameters is not compatible with default setup.

What I thought at first was to add PersistentPropertyBinaryPredicate with PredicateBinaryOp.ARRAY_CONTAINS. This doesn't have criterion handler but maybe I can squeeze in my custom logic just for PostgreSQL? I tried to do that, but it seems like I had no luck. If anyone could give some hints on how and where to squeeze.

The only thing I found when searching was that functions are not supported, which would probably help me, if they would be.

[dstepanov]

Try to use it like this

micronaut-data/doc-examples/mongo-example-groovy/src/main/groovy/example/PersonRepository.groovy

Line 84 in f2d77c8

return (root, criteriaBuilder) -> ((PersistentEntityCriteriaBuilder) criteriaBuilder).arrayContains(root.get("interests"), criteriaBuilder.literal(interest))

But at this moment I don’t now if we have it implemented for Postgres

[GeitV]

Thanks for quick answer! But I have to say that I've already tried that

    infix fun Expression<*>.arrayContains(others: List<Any>) =
        addPredicate(
            criteriaBuilder.arrayContains(
                this,
                criteriaBuilder.literal(others)
            )
        )

But this is not implemented for Postgres, getting following error:

Unexpected error occurred: ArrayContains is not supported by this implementation.
java.lang.UnsupportedOperationException: ArrayContains is not supported by this implementation.
	at io.micronaut.data.model.query.builder.AbstractSqlLikeQueryBuilder.lambda$new$11(AbstractSqlLikeQueryBuilder.java:276)
	at io.micronaut.data.model.query.builder.AbstractSqlLikeQueryBuilder.handleJunction(AbstractSqlLikeQueryBuilder.java:1137)
	at io.micronaut.data.model.query.builder.AbstractSqlLikeQueryBuilder.buildWhereClause(AbstractSqlLikeQueryBuilder.java:987)
	...

Can I somehow implement it myself, add criterion handler? I see that the method is protected, do I extend it somehow and override some internal bean?

[dstepanov]

Please try, check how others are implemented, it’s mostly requiring to register a new handler