如何在spring data jpa规范查询中结合distinct和sort与joins

Question

示例设置：

@Entity
class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Long? = null

    @ManyToMany(cascade = [CascadeType.PERSIST, CascadeType.MERGE])
    @JoinTable(name = "book_authors",
            joinColumns = [JoinColumn(name = "book_id")],
            inverseJoinColumns = [JoinColumn(name = "author_id")])
    var authors: MutableSet<Author> = HashSet()

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "publisher_id")
    lateinit var publisher: Publisher
}

fun hasAuthors(authorNames: Array<String>? = null): Specification<Book> {
    return Specification { root, query, builder ->
        query.distinct(true)
        val matchingAuthors = authorRepository.findAllByNameIn(authorNames)
        if (matchingAuthors.isNotEmpty()) {
            val joinSet = root.joinSet<Book, Author>("authors", JoinType.LEFT)
            builder.or(joinSet.`in`(matchingContentVersions))
        } else {
            builder.disjunction()
        }
    }
}

bookRepository.findAll(
    Specification.where(bookSpecification.hasAuthors(searchRequest)),
    pageable!!)

MockMvcRequestBuilders.get("/books?authors=Jane,John&sort=publisherName,desc")

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Order by expression "PUBLISHERO3_.NAME" must be in the result list in this case;

问题在于distinct和sort的结合。distinct要求发布服务器名称位于选择字段中，以便能够进行排序。

我如何用规范查询解决这个问题？

Answer 1

您可能必须显式地选择publishero3_.name列，如下所示：

java prettyprint-override">query.select(builder.array(root.get("PUBLISHERO3_.NAME"), root.get("yourColumnHere")));

默认情况下可能不包括联接列，因为它们超出了根泛型类型的范围。