使用sharding-jdbc时实体自关联问题如何解决？

Question

在使用JPA+sharding-jdbc时，遇到了实体自关联时查询错误问题，分析主要原因是sharding-jdbc做了查询优化，如果查询语句中使用分表列作为条件，就只会路由到1个分表中查询，不会查询所有分表，这对于自关联实体是不合理的。

User.java

@Entity
@Table("t_user")
@Data
@Builder
class User {

    @Id
    private Long id;

    private String name;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "t_parent_children",
        joinColumns = @JoinColumn(name = "parent", foreighKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT)),
        inverseJoinColumns = @JoinColumn(name = "children", foreignKey = @ForeignKey(name = "none", value = ConstraintMode.NO_CONSTRAINT)))
    @Builder.default
    private List<User> children = new ArrayList<>();

    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "children")
    @org.hibernate.annotations.ForeignKey(name="none")
    @Builder.default
    private List<User> parents = new ArrayList<>();
}

application.yml

spring:
  ...
  shardingsphere:
    datasource:
      ...
    sharding:
      tables:
        t_user:
          actual-data-nodes: dbname.t_user_$->{0..9}
          key-generator:
            type: SNOWFLAKE
            column: id
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: t_user_$->{id % 10}
    props:
      sql:
        show: true
  ...

测试数据

User father = User.builder().id(1).name("father").build();
userRepository.save(father);

User son = User.builder().id(2).name("son").build();
son.getParents().add(father);
father.getChildren().add(son);
userRepository.save(son);

User daughter = User.builder().id(3).name("daughter").build();
daughter.getParents().add(father);
father.getChildren().add(daughter);
userRepository.save(daughter);

UserRepository.java

@Repository
public interface UserRepository extends JpaRepository<User,Long> {
    
    @Query("select c from User p join p.children c where p.id = ?1")
    public List<User> findByParent(Long id);
}

测试用例

@Test
public void test() {
    List<User> children = userRepository.findByParent(1L);
    assertEquals(2, children.size()); // expect 2, but is 0
}

sharding日志

Actual SQL: slave ::: 
select user1.id as user1_id,user1.name as user1_name 
from t_user_1 user0 
inner join t_parent_children parent_children1 on arent_chidlren.parent = user0.id 
inner join t_user_1 user1 on parent_children1.children = user1.id 
where user0.id=? 
::: [1]

用户表中3条数据，根据分表规则分别在t_user_1,t_user_2,t_user_3表中，查询时根据father id=1查询，sharding只查询了t_user_1, 因此查不到son和daughter两条数据，如何解决呢？

Answer 1

这个问题后续如何解决的呢