JOOQ: How to map a record to an aliased table (from a nested query)

Question

I have a nested query with a join and I'm trying to map the results back to a table. The query is similar to this:

var nested = context.select(BUSINESS.fields()).from(BUSINESS).limit(size).offset(size * page);
var idField = nested.field(BUSINESS.UIDBUSINESSID);

var result = context.select(nested.fields()).select(BUSINESSALIAS.fields())
.select(BUSINESSCONTACT.fields()).select(BUSINESSIDENTIFIER.fields())
.select(SITEADDRESS.fields()).select(WEBSITE.fields())
.from(nested)
.leftJoin(BUSINESSALIAS)
.on(idField.eq(BUSINESSALIAS.BUSINESSID))
.leftJoin(BUSINESSCONTACT)
.on(idField.eq(BUSINESSCONTACT.BUSINESSID))
.leftJoin(BUSINESSIDENTIFIER)
.on(idField.eq(BUSINESSIDENTIFIER.BUSINESSID))
.leftJoin(SITEADDRESS)
.on(idField.eq(SITEADDRESS.BUSINESSID))
.leftJoin(WEBSITE)
.on(idField.eq(WEBSITE.BUSINESSID))
.fetch();

Mapping the results back to the BUSINESS like this record.into(BUSINESS).into(Business.class) causes a large number of exceptions because there are ambiguous matches for some fields. The actual error is:

Ambiguous match found for BusinessId. Both "alias_34775958"."BusinessId" and "MCDH"."dbo"."SiteAddress"."BusinessId" match

How to I tell JOOQ that the BUSINESS table qualifier should be the alias alias_34775958 when mapping the record into the table?

Answer 1

Your call to record.into(BUSINESS) causes this ambiguity, because your query doesn't really have any columns from the BUSINESS table (despite your using this table in a derived table).

Try using record.into(nested.fields()) instead, which will produce the exact field references including the generated alias. Alternatively, you can also use a table with an explicit alias:

var nested = table(select(BUSINESS.fields())
                   .from(BUSINESS)
                   .limit(size)
                   .offset(size * page)).as("b");

And now, that nested is of type Table<?> instead of Select<?>, you can use it directly in your record.into(nested) call.