How to use DSL.coalesce with lists of fields?-CodePudding

Using Jooq, I am trying to fetch from a table by id first, if no matches found, then fetch by handle again.

And I want all fields of the returned rows, not just one.

        Field<?> firstMatch = DSL.select(Tables.MY_TABLE.fields())
                .from(Tables.MY_TABLE.fields())
                .where(Tables.MY_TABLE.ID.eq(id))
                .asfield(); // This is wrong, because it supports only one field, but above we selected Tables.MY_TABLE.fields(), which is plural. 

        Field<?> secondMatch = DSL.select(Tables.MY_TABLE.fields())
                .from(Tables.MY_TABLE.fields())
                .where(Tables.MY_TABLE.HANDLE.eq(handle))
                .asfield(); // Same as above.

dslContext.select(DSL.coalesce(firstMatch, secondMatch))
          .fetchInto(MyClass.class);

Due to the mistake mentioned above in the code, the following error occurs:

Can only use single-column ResultProviderQuery as a field

I am wondering how to make firstMatch and secondMatch two lists of fields, instead of two fields?

I tried

        Field<?>[] secondMatch = DSL.select(Tables.MY_TABLE.fields())
                .from(Tables.MY_TABLE.fields())
                .where(Tables.MY_TABLE.HANDLE.eq(handle))
                .fields();

but the following error occurred in the line containing DSL.coalesce

Type interface org.jooq.Field is not supported in dialect DEFAULT

Thanks in advance!

CodePudding user response：

This sounds much more like something you'd do with a simple OR?

dslContext.selectFrom(MY_TABLE)
          .where(MY_TABLE.ID.eq(id))
          // The ne(id) part might not be required...
          .or(MY_TABLE.ID.ne(id).and(MY_TABLE.HANDLE.eq(handle))
          .fetchInto(MyClass.class);

If the two result sets should be completely exclusive, then you can do this:

dslContext.selectFrom(MY_TABLE)
          .where(MY_TABLE.ID.eq(id))
          .or(MY_TABLE.HANDLE.eq(handle).and(notExists(
              selectFrom(MY_TABLE).where(MY_TABLE.ID.eq(id))
          )))
          .fetchInto(MyClass.class);

If on your database product, a query using OR doesn't perform well, you can write an equivalent query with UNION ALL, which might perform better.