Help definining query in criteria builder

[rybandrei2014]

Hello,

how do I define the following query in Blaze persistence Criteria builder. Unfortunately, haven't found the exact use-case in documentation... Particularly I'm interested how to define FROM clause from subquery.

SELECT t1.id, t1.a, t1.b, t1.c
FROM table1 as t1
WHERE t1.id in (
    SELECT id FROM (
        SELECT t2.id, t2.order
        FROM table2 as t2
        ORDER BY CASE WHEN order IS NULL THEN id ELSE order END
        LIMIT 10 OFFSET 10
    )
)

Thank you.

[beikov]

You will have to use a @CTE entity as described in the documentation e.g. something like this:

@CTE
@Entity
class Table2Cte {
    @Id
    private Long id;
    private Long order;
}

then you can write something like this:

CriteriaBuilder<Tuple> cb = cbf.create(em, Tuple.class)
    .from(Table1Entity.class, "t1")
    .where("t1.id").in()
        .fromSubquery(Table2Cte.class, "c")
            .from(Table2Entity.class, "t2")
            .bind("id").select("t2.id")
            .bind("order").select("t2.order")
            .orderByAsc("coalesce(t2.order, t2.id)")
            .setFirstResult(10)
            .setMaxResults(10)
        .end()
        .select("c.id")
    .end()
    .select("t1.id")
    .select("t1.a")
    .select("t1.b")
    .select("t1.c");