How to save a jooq table as a variable and then reference it

Question

This is my sql expression:

select count(*) from source s
join(select id as I from source where name is not null) e
on s.name = e.I

Here is what I have in java

    Source s = SOURCE.as("s");
    TableLike<?> e = create.select(SOURCE.ID.as("I"))
            .from(SOURCE)
            .where(SOURCE.NAME.isNotNull()
            .asTable().as("e");

    create.selectCount()
    .from(s)
    .join(e)
    .on(s.NAME.equal(e.I))
    .fetchOne().value1();

I think TableLike is not the right type, because I get an error when I try to do eI

Answer 1

Unfortunately, there is (currently, as in jOOQ 3.2), there is no way to print column dereferencing types Ifrom your view e, since the Java compiler does not know that the Icolumn exists . You will have to resort to unsafe methods:

// Enforcing typesafety using coercion:
.join(e)
.on(s.NAME.equal(e.field("I").coerce(String.class))

// Circumventing typesafety using raw types
.join(e)
.on(s.NAME.equal((Field) e.field("I")))

Slightly more types of security can be achieved by reusing the field Ias such:

Source s = SOURCE.as("s");
Field<String> I = SOURCE.ID.as("I");
TableLike<?> e = create.select()
        .from(SOURCE)
        .where(SOURCE.NAME.isNotNull())
        .asTable().as("e");

create.selectCount()
      .from(s)
      .join(e)
      .on(s.NAME.equal(e.field(I)))
      .fetchOne().value1();