CODEWITHSUNDEEP
javapostgresqljooq
hololensen
hololensen

Reputation: 311

How to do GROUP BY with CASE in jOOQ?

I have query like this:

SELECT bm.name
FROM parent bm
ORDER BY CASE WHEN EXISTS
    (SELECT 1 FROM children bl 
              WHERE bl.user_id = 'd012345e-22sc-44e5-ade7-ffdce74af05e' 
                AND bl.parent_id=bm.parent_id)
    THEN 1 END;

Where I try to filter data with specific value from another table and it works fine, but I don't understand how to do this in jOOQ.

Upvotes: 1

Views: 341

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

I don't know what exactly you're struggling with, but remember that everything you attempt to do in jOOQ can almost always be translated 1:1 from SQL. If you're struggling with that, you can always use the online translation service to translate SQL to jOOQ (Java).

I'm assuming, the problems you're having is either related to:

Assuming you're using the code generator. and you have the usual static imports (e.g. import static org.jooq.impl.DSL.*;), write:

Parent bm = PARENT.as("bm");
Children bl = CHILDREN.as("bl");

ctx.select(bm.NAME)
   .from(bm)
   .orderBy(when(
       exists(
           selectOne()
           .from(bl)
           .where(bl.USER_ID.eq("d012345e-22sc-44e5-ade7-ffdce74af05e"))
           .and(bl.PARENT_ID.eq(bm.PARENT_ID))
       ),
       1
   ))
   .fetch();

Upvotes: 1

Related Questions