Provide a way to do DISTINCT ON of PostgresSQL

[aftabshk]

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:

1. 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 = ?

2. 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:

1. Providing a method in repository like findDistinctOnByType("id", "type").
2. Providing a method in CriteriaQuery like CriteriaQuery.distinctOn("id").

Or please suggest if there's any other workaround

[mp911de]

Spring Data JPA is not in charge of generating SQL from JPQL. How would you even express such a query in JPQL?

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.

[R6ASfcdT]

It's true, I also cannot realize distinct on column query where we have several columns. @aftabshk Have you found a workaround? I think it can only be a native query.

[R6ASfcdT]

Spring Data JPA is not in charge of generating SQL from JPQL. How would you even express such a query in JPQL?

I think that question was clear. We cannot use JPQL query with distinct on single column when query selects several. I think it might be:

entityManager.createQuery("select distinct(c.unit) e.id from Table e join fetch e.compositions c where c is not empty"