Get distinct column values with a Spring Data JpaRepository

Question

I have this entity (annotations omitted for brevity):

@Entity
class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Lob
    private String category;

    @Lob
    private String name;

    @Lob
    private String customer_number;

    // more attributes omitted
}

I have to get a list of distinct category value for a particular uid

In my JpaRepository I have this:

@Query("SELECT DISTINCT product.category FROM Product as product WHERE product.customerNumber = :cn ORDER BY product.category")
Page<String> findDistinctCategoryByCustomerNumber(String cn,
                                                  Pageable pageable);

Without the @Query annotation, the ids are returned, instead of the category values. The generated SQL looks like this:

select distinct product0_.id as id1_0_, product0_.customer_number as customer2_0_, product0_.category as directory3_0_, product0_.name as name4_0_ from product product0_ 
where product0_.customer_number=? 
order by product0_.id desc limit ?

But I need the distinct categories not the product entities. Short of another idea I added the @Query annotation above. But now I get this error:

Order by expression "PRODUCT0_.ID" must be in the result list in this case; SQL statement:
select distinct product0_.directory as col_0_0_ from product product0_ where product0_.customer_number=? order by product0_.directory, product0_.id desc limit ? [90068-197]

But I cannot add id to the result list because that would make the DISTINCT useless, as id is the primary key.

So I either need a suitable method name for automatic query generation or a way to stop JpaRepository from adding its order by clause to the end of my @Query.

order by is used for sorting the result set. If id is in the order by criteria but not the result set, then it won't work — Dovmo
@Dovmo I don't want to order by id. This is added by JpaRepository. The question is about how to avoid that. — Guido Flohr
The error you're getting above says your query contains the order by clause: ...order by product0_.directory, product0_.id desc limit ? [90068-197] — Dovmo
@Dovmo I know how ordering in sql works. But the order by id is added automatically, not by my code. I want to get rid of it. That is the question. — Guido Flohr
Spring Data repositories do not modify queries on their own. See this sample based on the question to verify this. Please show the real code where the problem exists. The current code has errors - the entity field is named customer_name, but is referred to as customerName in @Query. This would result in a different type of error. Also add the real code which calls findDistinctCategoryByCustomerNumber, showing how the Pageable parameter is passed. — manish

Jens Schauder Jens Schauder · Accepted Answer · 2019-01-15T07:14:17

The unwanted order by gets created since you are requesting a paged result. The notion of the n-th page only makes sense when your results are ordered and the ordering is coming from your Pageable method argument.

Remove the order by from your query and set the sort attribute of your Pageable to sort by category.

Get distinct column values with a Spring Data JpaRepository

1 Answers