CODEWITHSUNDEEP
javapostgresqljooq
TheShahin
TheShahin

Reputation: 153

JOOQ PostgreSQL join resulting in InvalidResultException

I'm trying to join two tables in JOOQ and output the result as a Map<String, Object>, but I get InvalidResultException("Field common_id is not unique in Record").

The code I'm executing is the following:

dslContext.select()
    .from(FOO)
    .join(BAR).using(FOO.COMMON_ID)
    .where(FOO.FOO_VALUE.eq(123))
    .limit(1)
    .fetchOne()
    .intoMap();

The generated SQL is something like this:

SELECT "public"."foo"."foo_id",
    "public"."foo"."common_id",
    "public"."foo"."foo_value",
    "public"."bar"."bar_id",
    "public"."bar"."common_id",
    "public"."bar"."bar_value",
    "public"."bar"."bar_value_two"
FROM "public"."foo"
    JOIN "public"."bar"
WHERE "public"."foo"."common_id" = ?
LIMIT ?;

I expected JOOQ to understand that the value I'm joining on will occur in both tables and omit one of them in the resulting Record, but it doesn't seem to do that.

Am I simply missing something blatantly obvious? What's the best/correct way to filter out the duplicate common_id from the resulting Record so that calling intoMap() returns a response instead of throwing an exception?

Upvotes: 1

Views: 555

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220932

This is a known issue: https://github.com/jOOQ/jOOQ/issues/2808

Unfortunately, the jOOQ object model produces duplicate columns from the JOIN .. USING syntax, as if you were using the JOIN .. ON syntax. The workaround is to list the columns explicitly in the SELECT clause, which is probably a good idea anyway in your case, as you might have accidental matches between any other column name as well (such as last_update, for example), or you can alias your table and its columns using Table.as(String, Function), generating a [table_name]__[column_name] scheme automatically.

Upvotes: 1

Tom Mac
Tom Mac

Reputation: 9853

You've selected two columns called common_id. Try aliasing one of them. For example :

 "public"."bar"."common_id" as bar_common_id

Upvotes: 1

Related Questions