Compare two arrays using QueryDSL

Question

Need assistance with QueryDSL predicate composition - how to write QueryDSL predicate to compare two arrays(find any UUID matches between two arrays) using && operator like this:

select '{e48f54d5-9845-4987-a53d-e0ecfe3dbb43}'::uuid[] && '{e48f54d5-9845-4987-a53d-e0ecfe3dbb43,4e9a43f2-cb23-4f1b-9f7f-c09687d97570}'::uuid[];

Using: Cockroach - v20.1.7, QueryDSL - v4.3.1

Tried the following way:

private BooleanBuilder createPredicates(QPlayer player, List<UUID> otherUuids) {
    predicates.and(player.listOfUuids.any().in(otherUuids)); // player.listOfUuids is type of ListPath<java.util.UUID, ComparablePath<java.util.UUID>>

    return predicates;
}

But it raise exception:

java.lang.IllegalStateException: name property not available for path of type COLLECTION_ANY. Use getElement() to access the generic path element.

Also tried to create booleanTemplate like this:

predicates.and(Expressions.booleanTemplate("{0} && '{{1}}'::uuid[]", player.listOfUuids, StringUtils.join(",", otherUuids)));

It returns such a SQL:

select ... where player.business_unit_ids && '{$1}'::uuid[]

But execution of it raise exception:

io.r2dbc.postgresql.ExceptionFactory$PostgresqlNonTransientResourceException: [08P01] received too many type hints: 1 vs 0 placeholders in query

Because it interpretates extra '{' and '}' which required to use to wrap it in uuid array as another placeholder. And it doesn't respect special symbol escaping or unicode also.

Any thoughts how two array comparison might be achieved using QueryDSL?

Answer 1

Figured out how to add desired predicate with && overlap operator:

predicates.and(Expressions.booleanTemplate("{0} && {1}::uuid[]", arg0, String.format("{%s}", arg1.stream().map(UUID::toString).collect(joining(",")))))

And it's working based on example query:

select '{e48f54d5-9845-4987-a53d-e0ecfe3dbb43,e48f54d5-9845-4987-a53d-e0ecfe3dbb45}'::uuid[] && '{e48f54d5-9845-4987-a53d-e0ecfe3dbb40,e48f54d5-9845-4987-a53d-e0ecfe3dbb45}'::uuid[];

I didn't found that QueryDSL will support && overlap operator in Ops.class that I will be able to write this predicate different way.