Insert values in many to many relationship tables with JOOQ

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?

I'm assuming you posted your entire code. In case of which:

You don't call execute on your USER_ID insertion

Simply add

insertUserIds.onConflictDoNothing().execute();

Or alternatively, fetch the generated IDs using a call to returning().fetch()

Inner join

This might just be a stylistic question, but what you seem to be doing is a cross join. Your INNER JOIN filters aren't really join predicates. I'd put them in the WHERE clause. Clarity may help avoid further problems in such a query.

Specifically, that first "join predicate" is very confusing, containing a CONCAT call, which isn't something one would see in an INNER JOIN every day, and only touches one table, not both:

.on(concat(USER_ID.USER_ID_TYPE, 
                val(CATEGORY_USER_ID_DELIMITER), 
                USER_ID.USER_ID_VALUE).in(subscriptionDTO.getUserId())

Wrong predicate

That last predicate seems wrong. You're inserting:

.set(SUBSCRIPTION.TOKEN, subscriptionDTO.getToken())

But you're querying

.and(SUBSCRIPTION.TOKEN.equal(subscriptionDTO.getContactId()))));

That should probably be subscriptionDTO.getToken() again

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()