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

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.

CodePudding user response：

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)
);