How to use DISTINCT, GROUP BY, ORDER BY in spring-data-jpa?

Question

Beware: this question has been falsely marked as duplicate. Anyways I found a solution that I will add to my question below, as answers cannot be posted anymore on "duplicate" questions.

---

I want to transform the following sql query into a JPA query using querydsl and spring-data-jpa:

SELECT DISTINCT age FROM `my_table`
  WHERE firstname = 'john' AND lastname = 'doe'
  GROUP BY age
  ORDER BY COUNT(*) DESC
  LIMIT 1

Currently I have the following, which just selects all entries by first+lastname:

public interface MyRepository extends JpaRepository<MyEntity, Long>, QueryDslPredicateExecutor<MyEntity> {

}

Usage:

@Autowired
private MyRepository dao;

//select * from my_table where firstname = ? and lastname = ?
BooleanExpression query = p.firstname.eq(firstname).and(p.lastname.eq(lastname));
dao.findAll(query);

//TODO how to add group, order and limit?

But I now want to group by age field and select the age that occurs the most. But how can I add DISTINCT, GROUP BY, ORDER BY COUNT and LIMIT statements in the querydsl?

---

Solution: it's not possible directly using CrudRepository, but still easy by using com.querydsl.jpa.impl.JPAQuery as follows:

QPersonEntity p = QPersonEntity.person; //the querydsl definition

int age = new JPAQuery(em)
    .select(p.age)
    .from(p)
    .where(p.firstname.eq(firstname).and(p.lastname.eq(lastname)))
    .groupBy(p.age)
    .orderBy(p.age.count().desc())
    .limit(1)
    .fetchOne();

Solution

You can define a method in your @Repository in order to expose a method that performs the required operation with a native query. In fact, as report in the Spring Data JPA reference docs:

The @Query annotation allows executing native queries by setting the nativeQuery flag to true.

You can add this code snippet in your @Repository:

@Query(value = "SELECT DISTINCT age "+
               "FROM my_table "+
               "WHERE firstname = :firstname AND lastname = :lastname " +
               "GROUP BY age " +
               "ORDER BY COUNT(*) DESC " +
               "LIMIT 1", nativeQuery = true)
int retrieveAgeByFirstNameAndLastName(@Param("firstname") String firstname,
                                      @Param("lastname") String lastname);

Comments

[Nadir]

I'm not sure if returning count and other information is an option unless you do some special projection class to cover that, maybe this stackoverflow.com/questions/43352967/… will help

[membersound]

I think querydsl supports keywords like group and distinct, but I don't know how to hook this into the QueryDslPredicateExecutor.

[Nadir]

apart from that you can do findDistinctByFirstNameAndLastNameOrderByFirstName(String first, String last, Pageable limit)

[membersound]

I know, but I have to "group by" and "order by count". How to achieve this using the functional approach?

[membersound]

@Jarrod Roberson this is not about projection. It's about how to use DISTINCT, GROUP BY, ORDER BY with spring-data-jpa! That's a totally different subject!

[Aritz]

Not a dup I guess. Apart from what the OP says, this question refers to how to implement it with querydsl, whereas in the linked question it's done using standard spring data repo.

[membersound]

if it's not possible with HQL/JPA, then this might probably fit best.