在实体中删除或添加关系时,同一内容类型的其他实体会重新排序相同的关系。
原因:我们正在更新其他实体的顺序,而我们应该只更新相反的顺序。
上下文:给定作者 -> 文章之间的双向关系,连接表:
| author_id | article_id | article_order | author_order
- `article_order` is the order of articles in author entities
- `author_order` is the order of authors in article entities
我们保留这两个订单,因为双方都有关系。因此,当更新作者并添加文章关系时,我们不仅更新article_order
,而且还更新author_order
(反面)。
在更新订单值的 SQL 查询之前是:
UPDATE
:joinTable: as a,
(
SELECT
id,
ROW_NUMBER() OVER ( PARTITION BY :joinColumn: ORDER BY :orderColumn:) AS src_order,
ROW_NUMBER() OVER ( PARTITION BY :inverseJoinColumn: ORDER BY :inverseOrderColumn:) AS inv_order
FROM :joinTable:
WHERE :joinColumn: = :id OR :inverseJoinColumn: IN (:inverseRelIds)
) AS b
SET :orderColumn: = b.src_order, :inverseOrderColumn: = b.inv_order
WHERE b.id = a.id;
问题是,与:inverseJoinColumn: IN (:inverseRelIds)
(使用相同关系的其他实体)的所有匹配也更新了 src_order 。
为了解决这个问题,查询被分成两部分:
-- Update order column
UPDATE :joinTable: as a,
(
SELECT
id,
ROW_NUMBER() OVER ( PARTITION BY :joinColumn: ORDER BY :orderColumn:) AS src_order,
FROM :joinTable:
WHERE :joinColumn: = :id
) AS b
SET :orderColumn: = b.src_order
WHERE b.id = a.id;
-- Update inverse order column
UPDATE :joinTable: as a,
(
SELECT
id,
ROW_NUMBER() OVER ( PARTITION BY :inverseJoinColumn: ORDER BY :inverseOrderColumn:) AS inv_order
FROM :joinTable:
WHERE :inverseJoinColumn: IN (:inverseRelIds)
) AS b
SET :inverseOrderColumn: = b.inv_order
WHERE b.id = a.id;
我们正在引入另一个数据库请求,但我没有看到解决此问题的任何其他方法。
为什么需要它? 如何测试呢?参见测试。
相关问题修复https://github.com/strapi/strapi/issues/16961