2

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[]) 

1 Answer 1

2

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

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you Lukas, nor google, nor github search, does not revealed this to doc to me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.