Home > Enterprise >  How to use DSL.coalesce with lists of fields?
How to use DSL.coalesce with lists of fields?

Time:11-04

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.

  • Related