Field.contains and postgresql arrays

[Max Kremer]

Hi Lukas,

I'm using postgresql9.5

  I ran into a stumbling block with trying to create a generic where clause that intelligently uses the CONTAINS operator. As described in the docs contains can be a convenience method for like and it can also render as the array contains operator @> for postgresql.

The problem I ran into is trying to do this dynamically. The issue can be described by the following pseudo code.

            String[] array = {"foo", "bar", "baz"};

                      

            Map<Field<Object>, Object> map = new HashMap<>();

            map.put(DSL.field("a"), 1);

            map.put(DSL.field("b"), array);

            map.put(DSL.field("c"), 2);

            Condition condition = map

                .entrySet()

                .stream()

                .reduce(

                    DSL.trueCondition(), 

                    (c, e) -> c.and(e.getKey().contains(inline(e.getValue()))), 

                    (c1, c2) -> c1.and(c2)

                );

            System.out.println(condition);

The above returns:

(

  1 = 1

  and a like ('%' || cast(1 as varchar) || '%') escape '!'

  and b like ('%' || cast(ARRAY['foo', 'bar', 'baz'] as varchar) || '%') escape '!'

  and c like ('%' || cast(2 as varchar) || '%') escape '!'

)

What I want is 

(

  1 = 1

  and a like ('%' || cast(1 as varchar) || '%') escape '!'

  and b  @> ARRAY['foo', 'bar', 'baz']

  and c like ('%' || cast(2 as varchar) || '%') escape '!'

)

Looking at the jooq source it looks the issue is line 96-97 class Contains<T>:

        // [#1107] Some dialects support "contains" operations for ARRAYs
        if (lhs.getDataType().isArray()) {
            return new PostgresArrayContains();
        }

The above code demands that the type of Field be defined. So Field<?> or Field<Object> won't work. When dynamically building SQL and mapping my specific domain model to Jooq I'm materializing all sort of clauses that are based on types determined at runtime. So potentially this is also an issue with Java Generics.... 

What would happen if the above were changed to :

        // [#1107] Some dialects support "contains" operations for ARRAYs
        if (lhs.getDataType().isArray() || rhs.getDataType().isArray()) {

            return new PostgresArrayContains();
        }

[Lukas Eder]

Hi Max,

I'm sorry for the delay.

I think that your suggestion is reasonable, at least not less reasonable than what we already have. I've registered a feature requests for this:

https://github.com/jOOQ/jOOQ/issues/5929

Of course, as always, the workaround here for the time being is to use plain SQL:

DSL.condition("{0} @> {1}", left, right);

Hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Thanks again for reporting. Issue https://github.com/jOOQ/jOOQ/issues/5929 is now fixed for 3.10 and will be merged into 3.9.2