In Jooq, how to make the “delete” statement return the deleted records?

In my project, there are 2 tables, orders and order_payments.

The latter is a join table with 2 columns, order_id and payment_id.

I didn’t use FK for efficiency sake.

When I delete some orders from orders table, I need to delete the related records from payments table based on the order_id field.

So, after the orders are deleted using the DELETE statement, I want the order_ids of the deleted orders to be returned, so that I can use them to retrieve the order_payments table and delete related records.

Could someone teach me how to do that?

I tried

    private List<OrdersRecord> deleteOrders(OrderQuery orderQuery, DSLContext dsl) {
        DeleteQuery<OrdersRecord> deleteQuery = dsl.deleteQuery(ORDERS);
        deleteQuery.addConditions(orderQueryConditions(orderQuery));
        deleteQuery.setReturning();
        deleteQuery.execute();
        return deleteQuery.getReturnedRecords();
    }

but an empty List was returned.

Where I did wrong?

Thanks in advance!

Answer

I don’t think this is possible in MySQL 8.0.21 yet: https://bugs.mysql.com/bug.php?id=83139

If you know a way to do this with native MySQL (whatever version), or emulate it somehow, please document it here: https://github.com/jOOQ/jOOQ/issues/6865