英文:
In Jooq, how to make the "delete" statement return the deleted records?
问题
在我的项目中，有两个表，orders 和 order_payments。
后者是一个连接表，有两个列，order_id 和 payment_id。
出于效率的考虑，我没有使用外键（FK）。
当我从 orders 表中删除一些订单时，我需要根据 order_id 字段从 payments 表中删除相关记录。
因此，在使用 DELETE 语句删除订单后，我希望被删除订单的 order_id 能够被返回，这样我就可以使用它们来检索 order_payments 表并删除相关记录。
有人可以教教我如何做吗？
我尝试过：
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();
}
但是返回了一个空的列表。
我做错了什么？
提前谢谢！
英文:
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!
答案1
得分: 1
这在MySQL 8.0.21中可能还不可行：
https://bugs.mysql.com/bug.php?id=83139
如果您知道如何在本机MySQL（无论版本如何）中实现此功能，或者以某种方式模拟它，请在此处记录：https://github.com/jOOQ/jOOQ/issues/6865
英文:
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
答案2
得分: 0
你可以使用'ON DELETE CASCADE'。这样你就可以解决你的问题。你不需要从第一个表中获取ID。你只需要从第一个表中执行删除查询，第二个表中的记录也将被删除。
例如：
https://www.mysqltutorial.org/mysql-on-delete-cascade/
英文:
You can use 'on delete cascade'. So you can resolve your problem. And you don't must getting ids from first table. You just must execute delete query from first table and records from second table will deleted too.
For example:
https://www.mysqltutorial.org/mysql-on-delete-cascade/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论