jOOQ array field contains any of input list values condition

Begging 0
Viewed 27

Using postgresql, I have a table product and the array field of the resticted_delivery_regions:

ALTER TABLE "product"
    ADD COLUMN IF NOT EXISTS "restricted_delivery_regions" CHARACTER VARYING(7)[];

COMMENT ON COLUMN "product"."restricted_delivery_regions"
    IS 'Prohibited regions for delivery, for example: {''US-NY'', ''US-NJ''}';

Using jOOQ on the code side, I'm trying to implement some specific condition like this (not compilant):

if (criteria.getRestrictedRegionsCount() > 0) {
    condition = condition.and(PRODUCT.RESTRICTED_DELIVERY_REGIONS.notContainsAnyOf(criteria.getRestrictedRegionsList()));
}

Where:

  1. PRODUCT.RESTRICTED_DELIVERY_REGIONS -- is autogenerated TableField<ProductRecord, String[]>, telling us the product's delivery resticted regions
  2. criteria.getRestrictedRegionsList() -- input of prohibited for delivery regions, by which products should be filtered

Questions are:

  1. Is it possible to code such a query using jOOQ means?
  2. If not, what is the best way to implement such a condition construct?

P.S. Field::notContains() is not an option here, because it looks for a match over the entire input array, not individual values

jooq java sql postgresql
1 Answers

In PostgreSQL, that's just the && operator. jOOQ doesn't support it natively, but as always, you can easily add support yourself using plain SQL templating:

condition.and("not ({0} && {1})", 
    PRODUCT.RESTRICTED_DELIVERY_REGIONS,

    // Pass the bind value using the DataType of the generated column,
    // for convenience
    val(criteria.getRestrictedRegionsList(), PRODUCT.RESTRICTED_DELIVERY_REGIONS)
);

This is assuming your list is already an array. If it isn't you'll have to convert it to an array, first.

As everything you do with jOOQ is effectively dynamic SQL, you can create a small library for such vendor-specific functions, e.g.

public static <T> Condition containsAny(
    Field<T[]> left,
    T... right
) {
    return containsAny(left, val(right, left));
}

public static <T> Condition containsAny(
    Field<T[]> left,
    Field<T[]> right
) {
    return DSL.condition("({0} && {1})", left, right);
}
Lukas Eder0
Related