Hi! How would write with DSL a where clause to che...

# exposed

[Vinicius Araujo]

Hi! How would write with DSL a where clause to check if array field contains value?:

select * from mytable where 'Journal'=ANY(pub_types);

where pub_types is a string array data type.

[jessel]

You want the inList operator, used the same as eq but with a collection type for the check

[Vinicius Araujo]

If anyone faces this in the future, this is what I had to do:

class AnyArrayOp<T>(private val expr1: Expression<*>, private val expr2: List<T>) : Op<Boolean>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        if (expr2.isEmpty()) {
            error("Cannot use an empty list to make an any array clause")
        }

        if (expr2.count() > 1) {
            append("(")
        }
        expr2.forEachIndexed { index, s ->
            if (index > 0) {
                append(" OR ")
            }
            append("'$s' = ANY(")
            append(expr1)
            append(")")
        }
        if (expr2.count() > 1) {
            append(")")
        }
    }
}

infix fun <T> ExpressionWithColumnType<List<T>>.any(array: List<T>): Op<Boolean> = AnyArrayOp(this, array)

[Chantal Loncle]

Hi @Vinicius Araujo Glad you were able to make it work with a custom operator.
The ANY and ALL operators are actually supported out-of-the-box as of version 0.48.0 by using anyFrom() or allFrom().
Here's an example of their use in the docs. The infix functions accept either array columns, subqueries, or just array/list types.

[Vinicius Araujo]

Thank you! that’s exactly what I was looking for.