Spring Boot - Jpa 独特且可分页

Question

我有一个使用 kotlin 的 Spring Boot v 2.1.8.RELEASE 项目。我们使用 postgres 数据库，我有以下 JPA 查询

    @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>

当我按原样运行它时 - 它工作得很好。当我对行业字段进行排序时（参见下面的对象） - 我收到以下错误

org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

相关对象的片段如下

申诉

@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

  }

行业

@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

}

Answer 1

尝试下面@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))")