CODEWITHSUNDEEP
javasqlpostgresqljooq
mat_boy
mat_boy

Reputation: 13686

JOOQ and PostgreSQL types in a DSL select

I have a problem with jOOQ 3.8. So, I have a table in PostgreSQL 9.5, something like.

CREATE TABLE my_table(
  id bigserial,
  types my_type[]
)

where the my_type is a type like

CREATE TYPE my_type AS(
  id smallint,
  something text
)

Now, in jOOQ 3.8 I want to do something like

dsl.selectDistinct(MY_TABLE.ID)
   .from(MY_TABLE)
   .join(TYPE_TABLE).on(TYPE_TABLE.ID.equal(DSL.any(
       MY_TABLE.TYPES.ID
   ))
   .fetch(MY_TABLE.ID);

Clearly the step in which I do MY_TABLE.TYPES.ID is wrong. I was thinking about using DSL.select(MY_TYPE.ID)... but clearly the my_type is a type, not a table.

How can I access type properties using jOOQ?

Upvotes: 2

Views: 441

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221135

How to solve this with PostgreSQL

I don't think there's an easy way to transform your my_type[] into an integer[] type in PostgreSQL, extracting my_type.id for each value, for it to be usable with the any() operator.

But you can work around this limitation by using UNNEST(), something like this:

SELECT DISTINCT my_table.id
FROM my_table
CROSS JOIN LATERAL unnest(my_table.types)

The above will yield something like

id   types                      id   something
----------------------------------------------
1    {"(1,a)","(2,b)"}          1    a
1    {"(1,a)","(2,b)"}          2    b
2    {"(1,a)","(2,b)","(3,c)"}  1    a
2    {"(1,a)","(2,b)"}          2    b
2    {"(1,a)","(2,b)"}          3    c

Now this, you can join again to TYPE_TABLE, such as:

SELECT DISTINCT my_table.id
FROM my_table
CROSS JOIN LATERAL unnest(my_table.types) types
INNER JOIN type_table ON type_table.id = types.id

Or, probably better performing:

SELECT my_table.id
FROM my_table
WHERE EXISTS (
  SELECT 1
  FROM type_table
  JOIN unnest(my_table.types) AS types ON type_table.id = types.id
)

How to solve this with jOOQ

jOOQ's unnest support is currently (as of version 3.8) rather simple, i.e. you don't get all the type information in the resulting table, which is why you need to do some plain SQL mingling. But it's certainly doable! Here's how:

create().select(MY_TABLE.ID)
        .from(MY_TABLE)
        .whereExists(
            selectOne()
            .from(unnest(MY_TABLE.TYPES).as("types", 
                 MY_TYPE.ID.getName(), 
                 MY_TYPE.SOMETHING.getName()
             ))
            .join(TYPE_TABLE)
            .on(TYPE_TABLE.ID.eq(field(name("types", MY_TYPE.ID.getName()), 
                                     MY_TYPE.ID.getDataType())))
        )
        .fetch(MY_TABLE.ID);

Upvotes: 1

Related Questions