Hibernate：spring jpa存储库中order by字段的额外交叉连接

Question

我在添加嵌套字段对象时遇到了一个Spring JPARepository的问题。

public Page<MeteoInfoDto> list(MeteoInfoFilter filter, Pageable pageable) {
    mapObjectDao.findByType(MapObjectType.Meteo, pageable);
}

@Query(value = "select m from MapObject as m left join fetch m.parent as parent left join fetch m.road as road left join fetch m.parentRegion as parentRegion left join fetch m.parentOrganization as parentOrganization where m.type = :type", countQuery = "select count(m.dbid) from MapObject m where m.type = :type")
Page<MapObject> findByType(@Param("type") MapObjectType type, Pageable pageable);

select m from MapObject as m left join fetch m.parent as parent left join fetch m.road as road left join fetch m.parentRegion as parentRegion left join fetch m.parentOrganization as parentOrganization where m.type = :type order by m.parent.shortDescription asc, m.shortDescription asc

select
        mapobject0_.dbid as dbid1_27_0_,
        ....
        mapobject4_.parent_org_id as parent_15_27_4_ 
    from
        map_object mapobject0_ 
    left outer join
        map_object mapobject1_ 
            on mapobject0_.parent_id=mapobject1_.dbid 
    left outer join
        map_object mapobject2_ 
            on mapobject0_.road_id=mapobject2_.dbid 
    left outer join
        map_object mapobject3_ 
            on mapobject0_.parent_region_id=mapobject3_.dbid 
    left outer join
        map_object mapobject4_ 
            on mapobject0_.parent_org_id=mapobject4_.dbid 

    cross join map_object mapobject5_

    where
        mapobject0_.parent_id=mapobject5_.dbid 
        and mapobject0_.type=? 
    order by
        mapobject5_.short_description asc,
        mapobject0_.short_description asc limit ?

//模型

public class MapObject extends DomainObject {

    private UUID id;
    private MapObjectType type;
    ...
    private MapObject parent;
    private MapObject parentRegion;
    private MapObject parentOrganization;
    private MapObject road;
    ...
    private Set<MapObject> children;
    private Set<MapObject> childrenRegion;
    private Set<MapObject> childrenOrganization;
}

为什么它为排序parent.shortdescription添加交叉联接map_object mapobject5_？

Answer 1

解决了！

    @Query(value = "select m from MapObject as m left join m.parent as parent left join fetch m.parent left join fetch m.road left join fetch m.parentRegion left join fetch m.parentOrganization where m.type = :type", countQuery = "select count(m.dbid) from MapObject m where m.type = :type")
Page<MapObject> findByType(@Param("type") MapObjectType type, Pageable pageable);

通过添加带有别名和无fetch的左联接，删除了交叉联接。

 select
        mapobject0_.dbid as dbid1_27_0_,
        mapobject2_.dbid as dbid1_27_1_,
        mapobject3_.dbid as dbid1_27_2_,
        mapobject4_.dbid as dbid1_27_3_,
        mapobject5_.dbid as dbid1_27_4_,
        mapobject0_.id as id2_27_0_,

        mapobject0_.short_description as short_de3_27_0_,       
        ...
        mapobject0_.parent_org_id as parent_15_27_0_,
        mapobject2_.id as id2_27_1_,
        ...
        mapobject2_.parent_org_id as parent_15_27_1_,
        mapobject3_.id as id2_27_2_,
        ....
        mapobject3_.parent_region_id as parent_14_27_2_,
        mapobject4_.short_description as short_de3_27_3_,
     ...      
        mapobject4_.parent_org_id as parent_15_27_3_,
        mapobject5_.id as id2_27_4_,
        mapobject5_.short_description as short_de3_27_4_,
       ...
        mapobject5_.parent_org_id as parent_15_27_4_ 
    from
        map_object mapobject0_ 
    left outer join
        map_object mapobject1_ 
            on mapobject0_.parent_id=mapobject1_.dbid 
    left outer join
        map_object mapobject2_ 
            on mapobject0_.parent_id=mapobject2_.dbid 
    left outer join
        map_object mapobject3_ 
            on mapobject0_.road_id=mapobject3_.dbid 
    left outer join
        map_object mapobject4_ 
            on mapobject0_.parent_region_id=mapobject4_.dbid 
    left outer join
        map_object mapobject5_ 
            on mapobject0_.parent_org_id=mapobject5_.dbid 
    where
        mapobject0_.type=? 
    order by
        mapobject0_.short_description asc,
        mapobject1_.short_description asc limit ?`enter code here`