I have a table like this called my_objects
:
| code | description | open | closed |
---- ----------- ---- ------
| 1 | first | 0 | 1 |
| 1 | first | 1 | 0 |
| 2 | second | 1 | 0 |
| 2 | second | 1 | 0 |
I'm returning a JSON object like this:
{
"totalItems": 2
"myObjs": [
{
"code": 1,
"description": "first",
"openCount": 1,
"closedCount": 1
},
{
"code": 2,
"description": "second",
"openCount": 2,
"closedCount": 0
}
],
"totalPages": 1,
"curentPage": 0
}
My query in my repository (MyObjsRepository.java
) looks like this:
@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);
This works, but I run into an issue when trying to sort by my aggregated columns. When I try to sort by openCount
, the Pageable
object will contain a org.springframework.data.domain.Sort
with an Order with the property openCount
. The log for my application shows what's going wrong (formatted for readability):
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 ?
The aliases aren't preserved, so I get the following error:
Caused by: org.postgresql.util.PSQLException: ERROR: column myObjs0_.openCount does not exist
I've tried renaming the sorting parameters, adding columns with the aliased names to my entity, and adding open and closed to the group by clause. I think I may be able to solve this with a native query, but I'd really rather not do that. Is there a way to resolve this issue without a native query?
Edit:
The MyObjs
entity looks like this:
@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 */
}
The MyObjsDto
looks like this:
@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 */
}
CodePudding user response:
Sort uses column which is present in the table. Here you are calculating it.
I would suggest you to explore and use @Formula
annotation to perform the same action.
@Formula("COUNT(CASE open WHEN 1 THEN 1 ELSE null END)")
private Integer open;
@Formula("COUNT(CASE closed WHEN 1 THEN 1 ELSE null END)")
private Integer closed;
and use this attributes to apply the sorting.