How to combine distinct and sort in spring data jpa specification query with joins

user_2472066 0
Viewed 1820

Example setup:

Entity

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

Both Author and Publisher are simple entities with just an id and a name.

The spring data jpa BookSpecification: (notice the distinct on query)

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()
        }
    }
}

Executing the query (pageable containing a sort on publisher.name)

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

The REST request:

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

This results in the following error:

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

The problem is in the combination of the distinct and sort. The distinct requires the publisher name to be in the select fields to be able to sort.

How can I fix this with Specification query?

spring-data spring-data-jpa kotlin java
2 Answers

You'll likely have to explicitly select the PUBLISHERO3_.NAME column like so:

query.select(builder.array(root.get("PUBLISHERO3_.NAME"), root.get("yourColumnHere")));

Joined columns are probably not included by default because they're out of scope with regards to the root generic type.

user_4252380

you can't do this. basically, if you have distinct and you want to sort, you can only use the selected columns.

what you can do is to use row_number() window function instead of distinct, and then select everything with row_number=1. you can find an (a little bit old) example here: https://stackoverflow.com/a/30827497/10668681

user_106686810
Related