CODEWITHSUNDEEP
postgresqlkotlinjooq
Neonailol
Neonailol

Reputation: 117

Cannot correctly build query with postgres array contains operator via JOOQ

I have trouble converting this query to jooq (it's a part of bigger query)

idea is to select only products that have all specified attributes

WITH "products" as (

    SELECT
      p.id,
      (
        SELECT coalesce(
            array_agg(attrs),
            ARRAY ['NO_RELATIONS']
        )
        FROM unnest(ARRAY [
                    tts.v,
                    ingredient.v,
                    variation.v
                    ]) AS "attrs"
        WHERE attrs IS NOT NULL
      ) AS "attributes"
    FROM t_product p
      LEFT JOIN LATERAL (SELECT 'PRODUCED' :: TEXT AS v
                         FROM t_tech_spech AS tts
                         WHERE tts.product_id = p.id
                         LIMIT 1) AS tts ON TRUE
      LEFT JOIN LATERAL (SELECT 'INGREDIENT' :: TEXT AS v
                         FROM t_tech_spech_item AS ingredient
                         WHERE ingredient.product_id = p.id
                         LIMIT 1) AS ingredient ON TRUE
      LEFT JOIN LATERAL (SELECT 'FOR_SALE' :: TEXT AS v
                         FROM t_product_variation AS variation
                         WHERE variation.catalog_product_id = p.id
                         LIMIT 1) AS variation ON TRUE
) SELECT id, "attributes"
FROM products
WHERE attributes @> ARRAY ['PRODUCED', 'INGREDIENT'];

i have done most part of it, no problems

val tts =
    dsl.select(inline("PRODUCED").cast(TEXT).`as`("value"))
        .from(T_TECH_SPECH)
        .where(T_TECH_SPECH.PRODUCT_ID.equal(product.ID))
        .limit(1)
        .asTable("tts")

val ingredient =
    dsl.select(inline("INGREDIENT").cast(TEXT).`as`("value"))
        .from(T_TECH_SPECH_ITEM)
        .where(T_TECH_SPECH_ITEM.PRODUCT_ID.equal(product.ID))
        .limit(1)
        .asTable("ingredient")

val variation =
    dsl.select(inline("FOR_SALE").cast(TEXT).`as`("value"))
        .from(T_PRODUCT_VARIATION)
        .where(T_PRODUCT_VARIATION.CATALOG_PRODUCT_ID.equal(product.ID))
        .and(T_PRODUCT_VARIATION.IS_DELETED.eq(false))
        .limit(1)
        .asTable("variation")

val attributes =
            dsl.select(coalesce(arrayAgg(field("attrs")), field(array("NO_RELATIONS"))))
                .from(
                    unnest(
                        array(
                            field("tts.value"),
                            field("ingredient.value"),
                            field("variation.value")
                        )
                    ).`as`("attrs")
                )
                .where(field("attrs").isNotNull).asField<Array<String>>("attributes")

dsl.with("products")
            .`as`(
                select(
                    product.ID,
                    field(attributes)
                ).from(product)
                    .leftJoin(lateral(tts)).on(trueCondition())
                    .leftJoin(lateral(ingredient)).on(trueCondition())
                    .leftJoin(lateral(variation)).on(trueCondition())
            )
            .with("products_filtered")
            .`as`(
                select(
                    product.ID,
                    field("products.attributes").`as`("attributes")
                ).from("products")
                    .join(product).on(product.ID.eq(field("products.id", UUID::class.java)))
                    .where(trueCondition())
                    .apply { attributesFilter(pAttributes) }
            )

private fun <T : Record> SelectConditionStep<T>.attributesFilter(pAttributes: List<ProductAttribute>) {
    if (pAttributes.isNotEmpty()) {
        val filter = pAttributes.joinToString(",") { StringUtils.wrap(it.toString(), "'") }
        and("attributes @> ARRAY [$filter]")
    }
}

and last part is function attributesFilter, which just concatenates values and paste it as raw sql

List<ProductAttribute> is list of enum values (for example FOR_SALE, INGREDIENT, PRODUCED)

i am missing function that can do 'array contains' operation in jooq

how can i write attributes @> ARRAY ['PRODUCED', 'INGREDIENT'] with it, using actual values from List<ProductAttribute>?

Edit 1

Tried function suggested by Lukas an it did not work for me

This is what i have in my attributesFilter right now

val field = this.field("products.attributes", TEXT.arrayDataType) 
and(field.contains(productAttributes.map { it.toString() }.toTypedArray()))

this implementation gives me this sql, which is not right: 

"alias_74630789"."products.attributes" @> cast('{"INGREDIENT","FOR_SALE"}' as varchar[])

Upvotes: 2

Views: 1309

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221285

I have good news for you. See the Javadoc of Field.contains(T):

If you're using SQLDialect.POSTGRES, then you can use this method also to express the "ARRAY contains" operator. For example:

// Use this expression
val(new Integer[] { 1, 2, 3 }).contains(new Integer[] { 1, 2 })

// ... to render this SQL
ARRAY[1, 2, 3] @> ARRAY[1, 2]

Regarding the unwanted varchar[] cast:

Note, you seem to have run into https://github.com/jOOQ/jOOQ/issues/4754. The workaround is to use plain SQL: https://www.jooq.org/doc/latest/manual/sql-building/plain-sql

Upvotes: 2

Related Questions