JOOQ query to return a boolean if all rows based on a field has different value on another field

user_10353383 0
Viewed 275

I am writing a JOOQ query. For example I have this table

ID Name Status
1 Erick Single
2 James Single
3 Erick Single
4 Erick Married

I want to check if all "Erick" is "Single". In this case, it should return false.

I tried this JOOQ query and I guess it works:

return !context.fetchExists(
        context.selectFrom(TABLE_NAME)
        .where(TABLE_NAME.NAME.eq("Erick"),
                TABLE_NAME.STATUS.ne("Single"))
        ));

Is there a more elegant approach? My goal is to immediately return if the pointer encounters an "Erick" that is "Married" and at the same time, the query is readable.

jooq sql
1 Answers

Your approach is perfectly fine, but I guess you are really looking for the EVERY aggregate function!

return context.fetchValue(
    select(every(TABLE_NAME.STATUS.eq("Single")))
    .from(TABLE_NAME)
    .where(TABLE_NAME.NAME.eq("Erick"))
);

Or even this. This is probably slower, than the above, depending on whether your RDBMS can still use indexes for this, but perhaps your actual query is more complex, and you might need to aggregate multiple values in one go:

return context.fetchValue(
    select(every(TABLE_NAME.STATUS.eq("Single"))
        .filterWhere(TABLE_NAME.NAME.eq("Erick")))
    .from(TABLE_NAME)
);
user_5217990
Related