Query using specifications gives error when ordering on joined column that is not in SELECT DISTINCT

Question

I get following error when doing a rather complicated query: for SELECT DISTINCT, ORDER BY expressions must appear in select list

In the query I need to find all distinct Requests that have an ExploitationSite that contains a search term in their dutch or french name. The result has to be ordered by the Activity's dutch name and limited to the first 10 for pagination.

To do this query I use the Page <T> findAll(Specification<T> spec, Pageable pageable) method of JpaSpecificationExecutor.

This will result in a SELECT DISTINCT query which has to be ORDERed BY a property that is not in SELECT. (details below)

I tried to fetch the activities eagerly in the hope it would place those differently in the SELECT. I did my best trying to get the DISTINCT in a subquery and then have the ORDER BY + LIMIT around that, but I did not succeed in that.

Has someone an idea how I can get this query to work?

The (simplified) Request entity

@Entity
@Table(name = "request_requests")
@History("Request")
public class Request extends EqualByStateObject {

    @GeneratedValue
    @Id
    private int id;

    @Embedded
    private RequestNumber requestNumber;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "fk_request")
    private List<ExploitationSite> exploitationSites = new ArrayList<>();

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(unique = true, name = "fk_activity")
    private Activity activity;

    ...
}

The Specification (I have to use distinct here because since a Request contains a List of ExploitationSites it was possible I got the same request multiple times back if multiple ExploitationSites contained the search term)

public class ExploitationSiteSpecification extends EqualByStateObject implements Specification<Request> {
    private final String exploitationSiteName;

    protected ExploitationSiteSpecification(String exploitationSiteName) {
        this.exploitationSiteName = exploitationSiteName;
    }

    @Override
    public Predicate toPredicate(Root<Request> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        query.distinct(true);
        ListJoin<Object, Object> exploitationSites = root.joinList("exploitationSites");
        return criteriaBuilder.or(
                criteriaBuilder.like(
                        criteriaBuilder.lower(exploitationSites.get("name").get("dutchName")), "%" + exploitationSiteName.toLowerCase() + "%"),
                criteriaBuilder.like(
                        criteriaBuilder.lower(exploitationSites.get("name").get("frenchName")), "%" + exploitationSiteName.toLowerCase() + "%")
        );
    }
}

The Pageable

public Pageable getPageable() {
    Sort sort = Sort.by(Sort.Order.asc("activity.name.dutchName"));

    PageRequest.of(0, 10, sort);
}

This results in a generated query like this one

select distinct request0_.id as id1_23_, 
    request0_.fk_activity as fk_acti15_23_,
    request0_.request_number as request12_23_

from request_requests request0_ 
    inner join request_exploitation_sites exploitati1_ on request0_.id=exploitati1_.fk_request 
    left outer join request_activity activity2_ on request0_.fk_activity=activity2_.id

where lower(exploitati1_.dutch_name) like $1 
        or lower(exploitati1_.french_name) like $2

order by activity2_.dutch_name asc limit $3

which then gives the for SELECT DISTINCT, ORDER BY expressions must appear in select list error

You need to select `dutch_name` also to order by that column — Eklavya, Aug 24 '20 at 20:08
I wish it was as easy as that, but it's difficult to change the query directly since it is automatically generated. — Dennis Heperol, Aug 25 '20 at 07:08
You can multiselect the column for distinct `query.multiselect(exploitationSites.get("id"),exploitationSites.get("name").get("dutchName")).distinct(true);` — Eklavya, Aug 25 '20 at 07:25
It looked promising, but it unfortunately did not change the generated query — Dennis Heperol, Aug 25 '20 at 08:33

score 0 · Answer 1 · answered Feb 16 '23 at 07:26

Assuming you put the distinct because the join with exploitationSites would return multiple rows, the following two options would work without using distinct.

right after the join you could do an additional fetch

ListJoin<Object, Object> exploitationSites = root.joinList("exploitationSites");
root.fetch("exploitationSites")

this would result in hibernate to create an additional join of ExploitationSites as well as selecting additional columns

select request0_.id as id1_23_, 
    request0_.fk_activity as fk_acti15_23_,
    request0_.request_number as request12_23_,
    exploitati3_.id as exploitati3_id,
    exploitati3_.name as exploitati3_name,
    ...

from request_requests request0_ 
    inner join request_exploitation_sites exploitati1_ on request0_.id=exploitati1_.fk_request 
    left outer join request_activity activity2_ on request0_.fk_activity=activity2_.id
    inner join request_exploitation_sites exploitati3_ on request0_.id=exploitati3_.fk_request 

where lower(exploitati1_.dutch_name) like $1 
        or lower(exploitati1_.french_name) like $2

order by activity2_.dutch_name asc limit $3

use fetch in the first place and cast it to Join

Join<Object, Object> exploitationSites = (Join<Object, Object>) root.fetch("exploitationSites");

By casting the Fetch to a Join you can still use where clauses.

Note that this will also select additional columns, but won't do an additional join in the resulting query.

In both cases the fetch will result in a join fetch which hibernate internally will remove duplicates from the parent entity (see https://stackoverflow.com/a/51177569)

Query using specifications gives error when ordering on joined column that is not in SELECT DISTINCT

1 Answers1