How to translate a MYSQL query to JooQ

I recently started working with JooQ and I want to implement the query below, but I was not able to do this.

insert into user_organization_role(id_user, id_organization, id_role,id_workspace)
with cte_0 as(select id_user
                     , id_organization
                     , id_role 
                     , id_workspace
              from user_organization_role
              where id_user = 0)
select distinct ur.id_user
                , cte_0.id_organization
                , cte_0.id_role
                , cte_0.id_workspace
from user_organization_role ur
     , cte_0
where ur.id_user <> 0
and (ur.id_user, cte_0.id_organization, cte_0.id_role, cte_0.id_workspace) not in (select id_user, id_organization, id_role, id_workspace from user_organization_role where id_user <> 0)

So far I have reached the next option, but I don't know how to continue and how to make it functional.

userDSLContext.insertInto(Tables.USER_ORGANIZATION_ROLE.asterisk(), DSL.with("cte_0")
                .as(DSL.select(Tables.USER_ORGANIZATION_ROLE.asterisk()).from(Tables.USER_ORGANIZATION_ROLE).where(Tables.USER_ORGANIZATION_ROLE.ID_USER.eq(0)))
                .selectDistinct(DSL.table(DSL.name("ur")).field("ID_USER"), DSL.table(DSL.name("cte_0")).field("ID_ORGANIZATION"),
                DSL.table(DSL.name("cte_0")).field("ID_ROLE"), DSL.table(DSL.name("cte_0")).field("ID_WORKSPACE"))
                .from(Tables.USER_ORGANIZATION_ROLE.as("ur"), DSL.table(DSL.name("cte_0"))).where(
                                DSL.table(DSL.name("cte_0")).field("ID_WORKSPACE").ne(DSL.inline(0)).and(
                                        DSL.table(DSL.name("ur")).field("ID_USER"), DSL.table(DSL.name("cte_0")).field("ID_ORGANIZATION"),
                                        DSL.table(DSL.name("cte_0")).field("ID_ROLE"), DSL.table(DSL.name("cte_0")).field("ID_WORKSPACE")
                                )
                ));

Solution

Let's look at the individual parts of your query

INSERT

The first problem is your INSERT statement. You wrote:

insertInto(Tables.USER_ORGANIZATION_ROLE.asterisk(), ...)

But there's no such method in the jOOQ API. Why did you write it this way? DSLContext.insertInto(Table<?>, ...) accepts a table first, not an org.jooq.QualifiedAsterisk.

You probably used the asterisk() method, because you thought this would somehow map to inserting into all columns? But you're not using USER_ORGANIZATION_ROLE.* as an expression in your SQL query either, so why do this?

Why not translate your original query directly:

// I'm just going to assume the usual static imports, to reduce "noise"
insertInto(USER_ORGANIZATION_ROLE,
    USER_ORGANIZATION_ROLE.ID_USER,
    USER_ORGANIZATION_ROLE.ID_ORGANIZATION,
    USER_ORGANIZATION_ROLE.ID_ROLE,
    USER_ORGANIZATION_ROLE.ID_WORKSPACE)

INSERT .. SELECT

You tried to pass the Select expression directly to the insertInto() method, but again, the jOOQ API doesn't have any such method that accepts a Select along with the table. The example from the manual seems clear?

create.insertInto(table)
      .select(select)
      .execute();

So, translated to your use-case:

// The WITH .. SELECT 
Select<Record4<.., .., .., ..>> select = ...

// The code from before
insertInto(USER_ORGANIZATION_ROLE,
    USER_ORGANIZATION_ROLE.ID_USER,
    USER_ORGANIZATION_ROLE.ID_ORGANIZATION,
    USER_ORGANIZATION_ROLE.ID_ROLE,
    USER_ORGANIZATION_ROLE.ID_WORKSPACE)

// Put your WITH .. SELECT in here
.select(select)

At this point, it's also worth noting that every query in jOOQ is a dynamic SQL query, so nothing stops you from assigning parts to local variables if that helps better structure and understand the individual parts of your query.

WITH .. SELECT

That part has a few more issues, including:

table(name("cte_0")).field("ID_ROLE") doesn't work this way. The Table<?> you're creating this way does not know anything about an ID_ROLE field, so it can't look it up. This is documented in the Javadoc. I.e.

The returned table does not know its field references, i.e. Fields.fields() returns an empty array.

You have to use field(name("cte_0", "ID_ROLE")) instead
Data types should be added to your field projections, so you get type safety and the correct projection when you execute the query, i.e. this would be better: field(name("cte_0", "ID_ROLE"), ID_ROLE.getDataType())

There may be other issues

A comment on when to use jOOQ

jOOQ works very well for complex queries, especially when they're dynamic. In some cases, namely when derived tables or CTE are involved, jOOQ can still handle the complexity, but the internal DSL approach makes the SQL statement a bit hard to read / write, mainly because it's not possible to reference a table that has not yet been declared. The whole type safety argument fails with derived tables or CTE when you write static SQL, i.e. non-dynamic SQL.

So, in the jOOQ world, whenever you're using derived tables or CTE, think about:

Whether you can use native SQL for the complex part, e.g. via plain SQL templating or a view, etc. (Seriously, just because you're using jOOQ doesn't mean you have to use it for every query)
Whether you can re-write your query to something much simpler (which is usually better irrespective of using jOOQ or not)

Rewriting the query to something simpler

Your query seems to be doing the same thing as this?

insert into user_organization_role (
  id_user, id_organization, id_role, id_workspace
)
select distinct
  ur.id_user,
  ur0.id_organization,
  ur0.id_role,
  ur0.id_workspace
from 
  user_organization_role ur,
  user_orgainzation_role ur0
where ur.id_user <> 0
and ur0.id_user = 0
and (ur.id_user, ur0.id_organization, ur0.id_role, ur0.id_workspace) not in (
  select id_user, id_organization, id_role, id_workspace
  from user_organization_role 
  where id_user <> 0
)

I don't think you needed the CTE in the first place. This seems exactly equivalent. I'm not quite sure about the use-case. You seem to be operating on a relationship table, and automatically add a relationship to the "default" organization/role/workspace for every user who doesn't have this yet. So, maybe, a classic INSERT IGNORE would even be better? I.e. like this?

insert ignore into user_organization_role (
  id_user, id_organization, id_role, id_workspace
)
select distinct
  ur.id_user,
  ur0.id_organization,
  ur0.id_role,
  ur0.id_workspace
from 
  user_organization_role ur,
  user_orgainzation_role ur0
where ur.id_user <> 0
and ur0.id_user = 0

This is assuming that the 4 columns form a UNIQUE constraint