spring data jpa - сортировка по полю присоединяемой сущности

Question

Есть 2 связанные Entity:

@Entity
@Table(name = "SETTING")
@Getter
@Setter
@FieldNameConstants
public class SettingEntity {
    @Id
    @Column(name = "SETTING_ID")
    private Long id;

    private String code;

    private String name;

    @ManyToOne
    @JoinColumn(name = "SCOPE_ID")
    private ScopeEntity scope;  
}

@Entity
@Table(name = "SCOPE")
@Getter
@Setter
@FieldNameConstants
public class ScopeEntity {
    @Id
    @Column(name = "SCOPE_ID")
    private Long id;

    private String code;
}

Репозиторий:

public interface SettingRepository extends JpaRepository<SettingEntity, Long>, JpaSpecificationExecutor<SettingEntity> {
    SettingEntity findByCode(String code);
}

Запрос с сортировкой используя спецификацию:

@Service
@RequiredArgsConstructor
public class SettingsSuvServiceImpl implements SettingsSuvService {
    private final SettingRepository repository;
    private final SettingSearchSpecificationBuilder specificationBuilder;
    private final ArmMapper mapper;

    @Override
    public Page<SettingDto> getSettingsFiltered(SettingFilterDto request, Pageable pageable) {
        PageRequest pageRequest = PagingUtils.orderIgnoreCase(pageable);
        Specification<SettingEntity> specification = createSearchSpecification(request);
        Page<SettingEntity> result = repository.findAll(specification, pageRequest);
        return result.map(mapper::settingToDto);
    }
}

public class PagingUtils {
    private PagingUtils() {}

    public static PageRequest orderIgnoreCase(Pageable pageable) {
        Sort sort = Sort.by(
            pageable.getSort().get()
                    .map(Sort.Order::ignoreCase)
                    .collect(Collectors.toList()));

        return PageRequest.of(pageable.getPageNumber(),
                              pageable.getPageSize(),
                              sort);
    }
}

Спецификация:

@Component
public class SettingSearchSpecificationBuilder {
    public Specification<SettingEntity> build(SettingFilterDto req) {
        Specification<SettingEntity> spec = Specification.where((root, query, criteriaBuilder) -> {
            return query.getRestriction();
        });

        if (req.getComponents() != null) {
            spec = spec.and(componentsAre(req.getComponents()));
        }
        if (req.getIoTypes() != null) {
            spec = spec.and(ioTypesAre(req.getIoTypes()));
        }
        if (req.getName() != null) {
            spec = spec.and(nameLike(req.getName()));
        }
        if (req.getCode() != null) {
            spec = spec.and(codeLike(req.getCode()));
        }
        if (req.getScopes() != null) {
            spec = spec.and(scopeIsIn(req.getScopes()));
        }
        if (req.getValue() != null) {
            spec = spec.and(valueLike(req.getValue()));
        }
        if (req.getValueTypes() != null) {
            spec = spec.and(valueTypeIsIn(req.getValueTypes()));
        }
        if (req.getDateLastChangeFrom() != null) {
            spec = spec.and(lastChangeFrom(req.getDateLastChangeFrom()));
        }
        if (req.getDateLastChangeTo() != null) {
            spec = spec.and(lastChangeTo(req.getDateLastChangeTo()));
        }

        return spec;
    }

Если выполнять запрос с сортировкой по полю code присоединенной сущности ScopeEntity:

SettingFilterDto(components=null, ioTypes=null, name=null, code=null, scopes=null, valueTypes=null, value=null, dateLastChangeFrom=null, dateLastChangeTo=null)
Page request [number: 0, size 2000, sort: scope.code: ASC]

, то запрос падает с ESQL Exception

... Caused by: Error : 1791, Position : 432, Sql = select * from ( select distinct settingent0_.setting_id as setting_id1_42_, settingent0_.code as code2_42_, settingent0_.last_change as last_change3_42_, settingent0_.name as name4_42_, settingent0_.scope_id as scope_id6_42_, settingent0_.value as value5_42_, settingent0_.value_type_id as value_type_id7_42_ from setting settingent0_ left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id order by lower(scopeentit1_.code) asc ) where rownum <= :1 , OriginalSql = select * from ( select distinct settingent0_.setting_id as setting_id1_42_, settingent0_.code as code2_42_, settingent0_.last_change as last_change3_42_, settingent0_.name as name4_42_, settingent0_.scope_id as scope_id6_42_, settingent0_.value as value5_42_, settingent0_.value_type_id as value_type_id7_42_ from setting settingent0_ left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id order by lower(scopeentit1_.code) asc ) where rownum <= ?, Error Msg = ORA-01791: выражение не выбрано

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

Если немного упростить запрос из ошибки, то получится, что вот в таком он виде не работает и это проверено в консоли:

select * from ( select distinct settingent0_.setting_id as setting, settingent0_.code as code, settingent0_.name as name4, settingent0_.scope_id as scope_id6_42_
from setting settingent0_
    left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id
order by lower(scopeentit1_.code) asc ) where ROWNUM <= 2000;

А например в таком виде все работает:

select * from ( select distinct settingent0_.setting_id as setting, settingent0_.code as code, settingent0_.name as name4, settingent0_.scope_id as scope_id6_42_
from setting settingent0_
    left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id
order by lower(name4) asc ) where ROWNUM <= 2000;

Если запрос выполнять по 1-му из полей самого SettingEntity:

SettingFilterDto(components=null, ioTypes=null, name=null, code=null, scopes=null, valueTypes=null, value=null, dateLastChangeFrom=null, dateLastChangeTo=null)
Page request [number: 0, size 2000, sort: code: ASC]

, то всё отрабатывает нормально.

Почему падает, когда сортировка идет по полю code присоединенной сущности ScopeEntity? Как это можно исправить? Судя по ответу https://translated.turbopages.org/proxy_u/en-ru.ru.a7c18b0d-62fe30ea-849f3d13-74722d776562/https/stackoverflow.com/questions/37536726/sort-by-joined-tables-field-spring-jpa она должна работать.

Используется БД Oracle.

Если взять неработающий sql-запрос формируемый Hibernate и убрать из запроса DISTINCT, то запрос работает:

select * from ( select settingent0_.setting_id as setting_id1_42_, settingent0_.code as code2_42_, settingent0_.last_change as last_change3_42_, settingent0_.name as name4_42_, settingent0_.scope_id as scope_id6_42_, settingent0_.value as value5_42_, settingent0_.value_type_id as value_type_id7_42_ from setting settingent0_
    left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id
order by lower(scopeentit1_.code) asc ) where rownum <=100;

но DISTINCT нам нужен. Если перенести DISTINCT из внутреннего SELECT во внешний SELECT, то запрос работает:

select distinct * from ( select  settingent0_.setting_id as setting_id1_42_, settingent0_.code as code2_42_, settingent0_.last_change as last_change3_42_, settingent0_.name as name4_42_, settingent0_.scope_id as scope_id6_42_, settingent0_.value as value5_42_, settingent0_.value_type_id as value_type_id7_42_ from setting settingent0_
    left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id
order by lower(scopeentit1_.code) asc ) where rownum <=100;

Но как Hibernate сказать об этом?

Если добавить в список выгружаемых полей сортируемое поле scope.code, то запрос работает:

select * from ( select distinct scopeentit1_.code, settingent0_.setting_id as setting_id1_42_, settingent0_.code as code2_42_, settingent0_.last_change as last_change3_42_, settingent0_.name as name4_42_, settingent0_.scope_id as scope_id6_42_, settingent0_.value as value5_42_, settingent0_.value_type_id as value_type_id7_42_ from setting settingent0_
    left outer join scope scopeentit1_ on settingent0_.scope_id=scopeentit1_.scope_id
order by lower(scopeentit1_.code) asc ) where rownum <=100;

Но как Hibernate сказать об этом?

Answer 1

Простой пример:

select * 
from t
order by a;

         A          B          C
---------- ---------- ----------
         1          2          3
         2          2          3

Как известно, сортировка выполняется на конечном результате SQL выражения (выборки). Посмотрим сначала на неотсортированный результат:

select distinct b, c 
from t;

         B          C
---------- ----------
         2          3

Возможно ли его отсортировать по колонке a? Нет, её нет в результате выборки, поэтому невозможно:

select distinct b, c
from t
order by a;

SQL Error: ORA-01791: not a SELECTed expression

Сообщение об ощибке говорит само за себя, колонка, по которой будет сортировка, должна присутствовать в выборке:

select distinct a, b, c 
from t
order by a

         A          B          C
---------- ---------- ----------
         1          2          3
         2          2          3

БЛОГ НА HUSL

spring data jpa - сортировка по полю присоединяемой сущности

Ответы (1 шт):