Insert values in many to many relationship tables with JOOQ

Question

I have three tables in my database, SUBSCRIPTION, USER_ID, and an association table called SUBSCRIPTION_USER_ID.

My strategy is to use JOOQ batch with three queries, the first one to insert on row into SUBSCRIPTION, the second query to insert multiple rows into USER_ID, and finally, I need to insert the association IDs into SUBSCRIPTION_USER_ID, so I did the following:

InsertValuesStep2 insertUserIds = insertInto(
    USER_ID, USER_ID.USER_ID_TYPE, USER_ID.USER_ID_VALUE);

for (String userId : subscriptionDTO.getUserId())
    insertUserIds = insertUserIds.values(getValue(0, userId), getValue(1, userId));

InsertReturningStep insertReturningUserIds = insertUserIds.onConflictDoNothing();

InsertResultStep insertReturningSubscription = insertInto(SUBSCRIPTION)
        .set(SUBSCRIPTION.CHANNEL_ID, subscriptionDTO.getChannel())
        .set(SUBSCRIPTION.SENDER_ID, subscriptionDTO.getSenderId())
        .set(SUBSCRIPTION.CATEGORY_ID, subscriptionDTO.getCategory())
        .set(SUBSCRIPTION.TOKEN, subscriptionDTO.getToken())
        .onConflictDoNothing()
        .returningResult(SUBSCRIPTION.ID);

Unfortunately, to insert values into the association table, I tried many ways but nothing works for me, finally, I tried to insert values in SUBSCRIPTION_USER_IDusing with select but It doesn't work:

InsertValuesStep insertValuesSubscriptionUserIds = insertInto(
        SUBSCRIPTION_USER_ID, 
        SUBSCRIPTION_USER_ID.SUBSCRIPTION_ID, 
        SUBSCRIPTION_USER_ID.USER_ID_ID)
    .select(select(SUBSCRIPTION.ID, USER_ID.ID)
        .from(SUBSCRIPTION)
        .innerJoin(USER_ID)
        .on(concat(USER_ID.USER_ID_TYPE, 
                val(CATEGORY_USER_ID_DELIMITER), 
                USER_ID.USER_ID_VALUE).in(subscriptionDTO.getUserId())
        .and(SUBSCRIPTION.SENDER_ID.equal(subscriptionDTO.getSenderId()))
        .and(SUBSCRIPTION.CHANNEL_ID.equal(subscriptionDTO.getChannel()))
        .and(SUBSCRIPTION.CATEGORY.equal(subscriptionDTO.getCategory()))
        .and(SUBSCRIPTION.TOKEN.equal(subscriptionDTO.getToken()))));

Am I missing something above? Is there a better way using JOOQ to insert many-to-many relationship values or to use queries results as parameters for other queries?

Answer 1

As mentioned above, I have inserted values for SUBSCRIPTION and USER_ID tables. And get for the association table I need to get the IDs of the already inserted values from the above two tables, so to solve the issue I've used this query to insert in SUBSCRIPTION_USER_ID:

InsertReturningStep insertReturningSubscriptionUserId = insertInto(
    SUBSCRIPTION_USER_ID, 
    SUBSCRIPTION_USER_ID.SUBSCRIPTION_ID, 
    SUBSCRIPTION_USER_ID.USER_ID_ID)
    .select(select(SUBSCRIPTION.ID, USER_ID.ID).from(SUBSCRIPTION
            .where(concat(USER_ID.USER_ID_TYPE, val(CATEGORY_USER_ID_DELIMITER), USER_ID.USER_ID_VALUE).in(subscriptionDTO.getUserId()))
            .and(SUBSCRIPTION.SENDER_ID.equal(subscriptionDTO.getSenderId()))
            .and(SUBSCRIPTION.CHANNEL_ID.equal(subscriptionDTO.getChannel()))
            .and(SUBSCRIPTION.CATEGORY.equal(subscriptionDTO.getCategory()))
            .and(SUBSCRIPTION.TOKEN.equal(subscriptionDTO.getToken()))).onConflictDoNothing();

Finally, I have executed all the queries using batch:

using(configuration).batch(insertReturningSubscription,
 insertReturningUserIds,
 insertReturningSubscriptionUserId).execute()