[FIXED] Spring Boot custom query order by aliased aggregate column

Issue

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 */
}

Solution

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.

Answered By - Rishal
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)