CODEWITHSUNDEEP
jooq
Kevin
Kevin

Reputation: 1657

Jooq multiset referencing foreign key relationship

I'm working off the example from the jooq blog post: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/. I've got the same setup: a foreign key relationship, and I want to load the Parent table and also get all rows that reference each of the Parent rows:

CREATE TABLE parent (
  id BIGINT NOT NULL,
   CONSTRAINT pk_parent PRIMARY KEY (id)
);

CREATE TABLE item (
  id BIGINT NOT NULL,
   parent_id BIGINT NOT NULL,
   type VARCHAR(255) NOT NULL,
   CONSTRAINT pk_item PRIMARY KEY (id),
   FOREIGN KEY (parent_id) REFERENCES parent (id)
);

This is what I think the jooq query should look like:

  @Test
  public void test() {
    dslContext.insertInto(PARENT, PARENT.ID).values(123L).execute();
    dslContext.insertInto(PARENT, PARENT.ID).values(456L).execute();

    dslContext.insertInto(ITEM, ITEM.ID, ITEM.PARENT_ID, ITEM.TYPE).values(1L, 123L, "t1").execute();
    dslContext.insertInto(ITEM, ITEM.ID, ITEM.PARENT_ID, ITEM.TYPE).values(2L, 456L, "t2").execute();

    var result = dslContext.select(
        PARENT.ID,
        DSL.multiset(
            DSL.select(
                ITEM.ID,
                ITEM.PARENT_ID,
                ITEM.TYPE)
                .from(ITEM)
                .join(PARENT).onKey()))
        .from(PARENT)
        .fetch();

    System.out.println(result);
  }

The result is that each Item shows up for each Parent:

Executing query          : select "PUBLIC"."PARENT"."ID", (select coalesce(json_arrayagg(json_array("v0", "v1", "v2" null on null)), json_array(null on null)) from (select "PUBLIC"."ITEM"."ID" "v0", "PUBLIC"."ITEM"."PARENT_ID" "v1", "PUBLIC"."ITEM"."TYPE" "v2" from "PUBLIC"."ITEM" join "PUBLIC"."PARENT" on "PUBLIC"."ITEM"."PARENT_ID" = "PUBLIC"."PARENT"."ID") "t") from "PUBLIC"."PARENT"
Fetched result           : +----+----------------------------+
                         : |  ID|multiset                    |
                         : +----+----------------------------+
                         : | 123|[(1, 123, t1), (2, 456, t2)]|
                         : | 456|[(1, 123, t1), (2, 456, t2)]|
                         : +----+----------------------------+
Fetched row(s)           : 2

I also tried using doing an explicit check for Parent.id == Item.parent_id, but it didn't generate valid SQL:

    var result =
        dslContext
            .select(
                PARENT.ID,
                DSL.multiset(
                    DSL.select(ITEM.ID, ITEM.PARENT_ID, ITEM.TYPE)
                        .from(ITEM)
                        .where(ITEM.PARENT_ID.eq(PARENT.ID))))
            .from(PARENT)
            .fetch();

Error:

jOOQ; bad SQL grammar [select "PUBLIC"."PARENT"."ID", (select coalesce(json_arrayagg(json_array("v0", "v1", "v2" null on null)), json_array(null on null)) from (select "PUBLIC"."ITEM"."ID" "v0", "PUBLIC"."ITEM"."PARENT_ID" "v1", "PUBLIC"."ITEM"."TYPE" "v2" from "PUBLIC"."ITEM" where "PUBLIC"."ITEM"."PARENT_ID" = "PUBLIC"."PARENT"."ID") "t") from "PUBLIC"."PARENT"]
org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar [select "PUBLIC"."PARENT"."ID", (select coalesce(json_arrayagg(json_array("v0", "v1", "v2" null on null)), json_array(null on null)) from (select "PUBLIC"."ITEM"."ID" "v0", "PUBLIC"."ITEM"."PARENT_ID" "v1", "PUBLIC"."ITEM"."TYPE" "v2" from "PUBLIC"."ITEM" where "PUBLIC"."ITEM"."PARENT_ID" = "PUBLIC"."PARENT"."ID") "t") from "PUBLIC"."PARENT"]
    at org.jooq_3.17.6.H2.debug(Unknown Source)

What am I doing wrong here?

Upvotes: 1

Views: 153

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

Correlated derived table support

There are numerous SQL dialects that can emulate MULTISET in principle, but not if you correlate them like you did. According to #12045, these dialects do not support correlated derived tables:

#12045 was fixed in jOOQ 3.18, producing a slighly less robust and more limited MULTISET emulation that only works in the absence of:

  • DISTINCT
  • UNION (and other set operations)
  • OFFSET .. FETCH
  • GROUP BY and HAVING
  • WINDOW and QUALIFY

But that probably doesn't affect 95% of all MULTISET usages.

Workarounds

Upvotes: 1

Related Questions