Stack Exchange
Spring Boot - Jpa Distinct and Pageable
I have a Spring Boot v 2.1.8.RELEASE project that uses kotlin.
We use a postgres database and I have the following JPA query
    @Query(value = " SELECT distinct grievance " +
               " FROM Grievance grievance" +
               " WHERE (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId) " +
               " OR (grievance.status=:status AND grievance.auditProgramPortal=:auditProgramPortal)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.publicSubmission=:publicSubmission)")
fun findAnalystBodyGrievances(
        auditProgramPortal: Boolean,
        organizationId: Long,
        status: GrievanceStatus,
        publicSubmission: Boolean,
        pageable: Pageable): Page<Grievance>

When I run this as is - it works as fine. When I sort on the industry field (see object below) - I get the following error
org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Snippets from the relevant objects are as follows
Grievance
@Entity
@Audited
@Table(name = "grievance")
class Grievance {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null

    @Column(name = "title", nullable = false)
    var title: String? = null

    @Column(name = "description", columnDefinition = "text")
    var description: String? = null

    @Column(name = "public_description", columnDefinition = "text")
    var publicDescription: String? = null

    @Column(name = "related_to_identified_risk")
    var relatedToIdentifiedRisk: Boolean? = null


    @OneToOne
    @JoinColumn(name = "industry_id")
    var industry: Industry? = null
  }

Industry
@Entity
@Table(name = "industry")
class Industry {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null

    @Column(name = "name", nullable = false, unique = true)
    var name: String? = null

    @Column(name = "color", nullable = false, unique = false)
    var color: String? = null
}

My question is - what is the easiest way to fix the query so that a Page is still returned
If I update my code to use the following Query - A generic object is returned
@Query(value = " SELECT distinct grievance, grievance.industry " +
               " FROM Grievance grievance" +
               " WHERE (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId) " +
               " OR (grievance.status=:status AND grievance.auditProgramPortal=:auditProgramPortal)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.publicSubmission=:publicSubmission)")

Any help on this is much appreciated

I used the following trick - I introduce @Formula (theExpressionThere) as a filed in the entity and used sort by the expression.

thanks @StanislavL - have you any useful examples on how to use this?

What do you pass in your pageable argument?

@KamilW that is an instance of a org.springframework.data.domain.Pageable object

If you remove distinct in original jpql then does it sort? can  you share how you build pageable? when industry is part of grievance then it should not throw that error.

What if you move sorting to jpql instead of in pageable

have you put it like this? Sort.by("industry") or Sort.by("grievance.industry")?

I guess you are using Sort inside Pageable. May you show how you create the Pegeable instance?

I create Pageable by accepting a Pageable object in my controller and passing the following params along with my get request as query params pageIndex: number,
pageSize: number,
sortField: string,
sortOrder: string

try below
@Query(value =" Select grievance from (SELECT distinct grievance, grievance.industry " +
" FROM Grievance grievance" +
" WHERE (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId) " +
" OR (grievance.status=:status AND grievance.auditProgramPortal=:auditProgramPortal)" +
" OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId)" +
" OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.publicSubmission=:publicSubmission))")


A possible solution but I hope to get away with using 1 query. Thanks for your reply

thats first thing came in my mind but faster approach as all operations will execute at database server, I will add if something else come to my mind

I tried this, but JPA doesn't support FROM (SELECT ...

@Purfakt can you post a fresh post explaining your problem and what you have tried and what do you want to achieve
