Selecting fields to join on when joining nested queries

Question

I've recently been migrating my database and have been moving our remaining SQL queries over to jooq. Having some fun with this one:

private SelectQuery<?> getIdeasQuery() {
    Select<?> numComments = dslContext.select(DSL.count().as("comment_count"), COMMENT.IDEA_ID).from(COMMENT).groupBy(COMMENT.IDEA_ID);
    Select<?> numLikes = dslContext.select(DSL.count().as("like_count"), USER_LIKES_IDEA.IDEA_ID).from(USER_LIKES_IDEA).groupBy(USER_LIKES_IDEA.IDEA_ID);

    return dslContext
      .select(DSL.field("comment_count").as("num_comments"))
      .select(DSL.field("like_count").as("num_likes"))
      .select(USER.DISPLAY_NAME)
      .select(USER.AUTHORITY)
      .select(IDEA.fields())
      .from(IDEA.leftOuterJoin(numComments).on(COMMENT.IDEA_ID.eq(IDEA.ID))
                .leftOuterJoin(numLikes).on(USER_LIKES_IDEA.IDEA_ID.eq(IDEA.ID))
                .leftOuterJoin(USER).on(IDEA.USER_ID.eq(USER.ID)))
      .getQuery();
}

The returned query is then used to append additional modifiers (using addConditions(), addOrderBy(), addLimit() etc.) depending on the context, and then executed.

The issue I'm having is that the two sub-select queries are not named as their original names for the joins. This is correct behaviour! However, within jOOQ I'm having a hard time finding how I can specify the sub-query's fields to join on. I've tried going down the route of renaming the sub-queries as described in this StackOverflow answer, but the types don't line up for me.

Any ideas?

Answer 1

So I've found what I think is a solution for now. However, it seems like a workaround. I've given the sub-queries names when I do the join, and using that name to point to the field. If my schema changes in future, I don't think this will flag up a compile time error.

Here it is for anyone interested:

private SelectQuery<?> getIdeasQuery() {
    Select<?> numComments = dslContext.select(DSL.count().as("comment_count"), COMMENT.IDEA_ID).from(COMMENT).groupBy(COMMENT.IDEA_ID);
    Select<?> numLikes = dslContext.select(DSL.count().as("like_count"), USER_LIKES_IDEA.IDEA_ID).from(USER_LIKES_IDEA).groupBy(USER_LIKES_IDEA.IDEA_ID);

    return dslContext
          .select(DSL.field("comment_count").as("num_comments"))
          .select(DSL.field("like_count").as("num_likes"))
          .select(USER.DISPLAY_NAME)
          .select(USER.AUTHORITY)
          .select(IDEA.fields())
          .from(IDEA.leftOuterJoin(numComments.asTable("com")).on(DSL.field("com.idea_id").eq(IDEA.ID))
                    .leftOuterJoin(numLikes.asTable("like")).on(DSL.field("like.idea_id").eq(IDEA.ID))
                    .leftOuterJoin(USER).on(IDEA.USER_ID.eq(USER.ID)))
          .getQuery();
}