Distinct results from Spring Data JPA Specification by one column

Question

I have the following table on database:

id	employee_id	title	created_at
1	10	title1	2022-10-10
1	10	title2	2022-10-11

I want fetch data by distinct on title column;

by sql I can do this:

select distinct on (employee_id) *
from contracts
order by employee_id, created_at desc

The size of result is one and it is correct:

id	employee_id	title	created_at
1	10	title2	2022-10-11

But when I want implement this by psecification, cannot distinct result by column:

Specification { root: Root<Contract>, query: CriteriaQuery<*>, criteriaBuilder: CriteriaBuilder ->
            val firstNamePredicate: Predicate =
                    criteriaBuilder.equal(root.get<String>("employee").get<String>("id"), "1")
            query.distinct(true)
            criteriaBuilder.and(firstNamePredicate)
        }

This query return all rows that employee_id is equals 1.

I applied below changes, but result not changed:

query.select(root.get<String>("employee").get("id")).distinct(true)

How can I apply distinct on column?

What makes you think both are the same? Your first attempt is only adding a where clause on `employee_id` not a distinct on a certain column. The second does only a `select employee_id` with a distinct. So they aren't even remotely the same. However I'm not aware that you can do this with JPA as this seems to be a Postgres feature. To mimic you could write a query that selects the top most employee record for a certain id. As that looks like what you want to achieve. — M. Deinum, Feb 28 '23 at 14:17
@Diego Yes. I will try to explain the solution under this post. — mgh, Jul 15 '23 at 09:02

score 0 · Accepted Answer · answered Jul 16 '23 at 14:56

Another solution in SQL is to use max function. In this question, I want to find employee by bigger created_at value. I can use follow SQL query:

select employee_id, max(created_at)
from contracts
group by employee_id;

Following the above answer, I used the below solution on Specification:

Subquery<Long> sub = q.subquery(Long.class);     
Root<Contract> subRoot = sub.from(Contract.class);     
sub.select(cb.max(subRoot.get("created_at")))
   .where(cb.equal(root.get("employee_id"), subRoot.get("employee_id")));
return cb.equal(root.get("created_at"), sub);

Distinct results from Spring Data JPA Specification by one column

1 Answers1