Provide a way to do DISTINCT ON of PostgresSQL #3500
Description
I am using following versions:
PostgresSQL - 14.5
Spring Boot - 3.1.11
Spring Data Jpa - 3.1.11
I have two Entity classes like below:
@Entity
@Table(name = "quote")
@JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
public class Quote {
@Id
public Long id;
public String quote;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "author_id")
public Author authorEntity;
public String type;
}
@Entity
@Table(name = "author")
@JsonIgnoreProperties({ "hibernateLazyInitializer", "handler" })
public class Author {
@Id
public Long id;
public String name;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "country_id")
public Country country;
}
I want to perform a distinct query based on a particular column like below. In postgres this gives me rows by performing a distinct only on id column.
select distinct on (q.id) q.id, q.quote, q.author_id, a.name from quote q join author a on q.id = a.id
I have tried two ways so far:
- Using distinctBy method inside Repository
@Repository
public interface QuoteRepository extends JpaRepository<Quote, Long> {
List<Quote> findDistinctByType(String type);
}
But this will create a query like below which is not what I require. This query applies distinct over all the projections
select distinct q.id, q.quote, q.author_id, a.name from quote q join author a on q.type = ?
- I tried using distinct() method of CriteriaQuery class like below:
public class QuoteSpecification implements Specification<Quote> {
@Override
public Predicate toPredicate(Root<Quote> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
query.select(root.get("id")).distinct(true);
return criteriaBuilder.equal(root.get("type"), "PEACE");
}
}
This will also create a query like below which is not what I require:
select distinct q.id, q.quote, q.author_id, a.name from quote q join author a on q.type = ?
Is there any way to do this? Something like below:
- Providing a method in repository like
findDistinctOnByType("id", "type"). - Providing a method in CriteriaQuery like CriteriaQuery.distinctOn("id").
Or please suggest if there's any other workaround