Skip to main content

With

Definition​

The with method allows you to add the WITH clause to the query.

Available methods​

1. with(KCommonTableExpressionFilled... kCommonTableExpressionsFilled)​

  • kCommonTableExpressionsFilled: are all Common Table Expressions or CTEs that will be added to the WITH clause.

Method hierarchy​

The with method can be used right after the following methods or objects:

and the subsequent methods that can be called are:

Example​

Java code:

final KValues userIdsValues =
    values()
    .append(new ArrayList<>() {{
        add(2L);
    }})
    .append(new ArrayList<>() {{
        add(3L);
    }});
        
final KCommonTableExpressionFilled userIdsCte = 
    cte("user_ids_cte")
    .columns("id")
    .as(userIdsValues, "uic");

k
.with(userIdsCte)
.select(
    boolAnd(
        exists(
            k
            .select1()
            .from(APP_USER)
            .where(APP_USER.ID.eq(userIdsCte.c("id")))
        )
    )
)
.from(userIdsCte)
.single(Boolean.class);

SQL generated:

WITH user_ids_cte (id) AS (
    VALUES (?1), (?2)
) 
SELECT BOOL_AND (
    EXISTS (
        SELECT ?3
        FROM app_user au
        WHERE au.id = uic.id
    )
) FROM user_ids_cte uic

Parameters:

  • ?1: 2
  • ?2: 3
  • ?3: 1