How to make "SELECT something IN (...)" with jooq?

Question

I am trying to do the following with Jooq and cannot for the life of me figure out how to do this correctly:

select name, id in ( select capability_id from a.capabilities_users where user_id = ?) from a.capabilities; 

Basically, I want to get all the elements (features) and find out if each belongs to a specific user. It seems that all condition type operators (for example, greater than or in) can only be used in that place, and not in the selection. And I can’t figure out how else to express it.

In the worst case, I can do the counting and then execute the logic in Java, but I was hoping to use fetchMap.

Answer 1

Depending on the database and schema metadata, a LEFT JOIN may be a better choice than a projection predicate. You should, of course, verify this in terms of implementation.

Solving this with LEFT JOIN :

 -- NVL2 is Oracle syntax. -- jOOQ will emulate NVL2 using CASE, if it not available in your database SELECT c.name, NVL2(cu.capability_id, 1, 0) FROM a.capabilities c LEFT OUTER JOIN a.capabilities_users cu ON (c.id = cu.capability_id AND cu.user_id = ?) 

The above assumes, of course, that there is an undefined restriction on cu(user_id, capability_id) . This then translates to jOOQ as such:

 Capabilities c = CAPABILITIES.as("c"); CapabilitiesUsers cu = CAPABILITIES_USERS.as("cu"); Field<String> key = c.NAME.as("key"); Field<Boolean> value = nvl2( CAPABILITIES_USER.CAPABILITY_ID, true, false ).as("value"); Map<String, Boolean> map = DSL.using(configuration) .select(key, value) .from(c) .leftOuterJoin(cu) .on(c.ID.eq(cu.CAPABILITY_ID)) .and(cu.USER_ID.eq(...)) .fetchMap(key, value); 

Solving this with a predicate in projection:

If you really prefer a predicate in projection, you can try DSL.field(Condition) , which allows exactly this:

 Field<String> key = CAPABILITIES.NAME.as("key"); Field<Boolean> value = field( CAPABILITIES.ID.in( select(CAPABILITY_ID) .from(CAPABILITIES_USERS) .where(CAPABILITIES_USERS.USER_ID.eq(...)) ) ).as("value"); Map<String, Boolean> map = DSL.using(configuration) .select(key, value) .from(CAPABILITIES) .fetchMap(key, value); 

Please note: if you use a standard database that does not allow predicates to be viewed as columns, DSL.field(Condition) will make the equivalent CASE statement for you.