Spring Boot自定义查询按聚合列别名排序报错问题

我有一张名为my_objects的表，结构如下：

| code | description | open | closed |
+ ---- + ----------- + ---- + ------ +
| 1    | first       | 0    | 1      |
| 1    | first       | 1    | 0      |
| 2    | second      | 1    | 0      |
| 2    | second      | 1    | 0      |

需要返回如下格式的JSON：

{
    "totalItems": 2,
    "myObjs": [
        {
            "code": 1,
            "description": "first",
            "openCount": 1,
            "closedCount": 1
        },
        {
            "code": 2,
            "description": "second",
            "openCount": 2,
            "closedCount": 0
        }
    ],
    "totalPages": 1,
    "curentPage": 0
}

在MyObjsRepository.java中使用如下JPQL查询：

@Query(
    value = "SELECT new myObjs(code, description, "
    + "COUNT(CASE open WHEN 1 THEN 1 ELSE null END) as openCount "
    + "COUNT(CASE closed WHEN 1 THEN 1 ELSE null END) as closedCount) "
    + "FROM MyObjs "
    + "GROUP BY (code, description)"
)
Page<MyObjs> findMyObjs(Pageable pageable);

查询能正常返回数据，但按聚合列openCount排序时出错。日志生成的SQL如下：

select 
    myObjs0_.code as col_0_0_, 
    myObjs0_.description as col_1_0_, 
    count(case myObjs0_.open when 1 then 1 else null end) as col_2_0_,
    count(case myObjs0_.closed when 1 then 1 else null end) as col_3_0_
from my_objects myObjs0_
group by (myObjs0_.code, myObjs0_.description)
order by myObjs0_.openCount asc limit ?

报错信息：

Caused by: org.postgresql.util.PSQLException: ERROR: column myObjs0_.openCount does not exist

已尝试修改排序参数名称、为实体添加对应列、将open/closed加入GROUP BY等方法，希望不使用原生查询解决问题。

补充实体类MyObjs：

@Entity
@Table(schema = "my_schmea", name = "my_objects")
public class MyObjs {
    @Column(name = "code")
    private Integer code;

    @Column(name = "description")
    private String description;

    @Column(name = "open")
    private Integer open;

    @Column(name = "closed")
    private Integer closed;

    /* getters, setters, and constructor */
}

补充DTO类MyObjsDto：

@JsonAutoDetect(getterVisibility = JsonAutoDetect.Visibility.PUBLIC_ONLY)
public class MyObjsDto {
    @JsonProperty(value = "code")
    private String code;

    @JsonProperty(value = "description")
    private String description;

    @JsonProperty(value = "openCount")
    private String open;

    @JsonProperty(value = "closedCount")
    private String closed;

    /* getters, setters, and constructor */
}

方案1：直接用聚合表达式构造排序 #

Spring Data JPA的Sort支持传入JPQL表达式，而非仅属性名。构造Sort时直接使用聚合的CASE语句片段即可：

Sort sort = Sort.by(Sort.Order.asc("COUNT(CASE open WHEN 1 THEN 1 ELSE null END)"));
Pageable pageable = PageRequest.of(0, 10, sort);
myObjsRepository.findMyObjs(pageable);

这样生成的SQL会把排序条件替换为对应的聚合表达式，不会再去查询不存在的openCount列。

方案2：自定义排序参数转换器 #

1. 先修正JPQL查询，确保正确构造DTO（注意new myObjs要替换成完整的DTO类路径，参数顺序要和DTO构造器匹配）：

@Query(
    value = "SELECT new com.yourpackage.MyObjsDto(m.code, m.description, "
    + "COUNT(CASE m.open WHEN 1 THEN 1 ELSE null END), "
    + "COUNT(CASE m.closed WHEN 1 THEN 1 ELSE null END)) "
    + "FROM MyObjs m "
    + "GROUP BY m.code, m.description"
)
Page<MyObjsDto> findMyObjs(Pageable pageable);

2. 自定义SortHandlerMethodArgumentResolver，将DTO的openCount/closedCount映射为对应的聚合表达式：

@Component
public class CustomSortResolver extends SortHandlerMethodArgumentResolver {
    @Override
    public Sort resolveArgument(MethodParameter parameter, ModelAndViewContainer mavContainer, NativeWebRequest webRequest, WebDataBinderFactory binderFactory) {
        Sort sort = super.resolveArgument(parameter, mavContainer, webRequest, binderFactory);
        if (sort == null) return sort;

        List<Sort.Order> orders = new ArrayList<>();
        for (Sort.Order order : sort) {
            String property = order.getProperty();
            switch (property) {
                case "openCount":
                    orders.add(new Sort.Order(order.getDirection(), "COUNT(CASE m.open WHEN 1 THEN 1 ELSE null END)"));
                    break;
                case "closedCount":
                    orders.add(new Sort.Order(order.getDirection(), "COUNT(CASE m.closed WHEN 1 THEN 1 ELSE null END)"));
                    break;
                default:
                    orders.add(order);
            }
        }
        return Sort.by(orders);
    }
}

3. 在配置类中注册这个自定义解析器：

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {
    @Autowired
    private CustomSortResolver customSortResolver;

    @Override
    public void addArgumentResolvers(List<HandlerMethodArgumentResolver> resolvers) {
        resolvers.add(customSortResolver);
    }
}

这样前端传入sort=openCount,asc时，会自动转换成对应的聚合排序表达式。

方案3：引入Blaze-Persistence扩展（可选） #

如果允许引入第三方库，Blaze-Persistence作为JPA的扩展，支持更灵活的JPQL查询和排序，能直接识别DTO中的别名并转换排序条件，无需额外编写转换逻辑。

内容的提问来源于stack exchange，提问作者Briana