Group by clause with QuerydslPredicateExecutor

Question

I have an entity as below:

@Entity
public class Enterprise{
  private String sid = UUID.randomUUID().toString();
  private String name;
  private String organisationSid;
  private EnterpriseStatus status;
  @CreatedDate
  private Date dateCreated;
}

Repository:

public interface EnterpriseRepository extends 
            PagingAndSortingRepository<Enterprise, String>, 
            QuerydslPredicateExecutor<Enterprise>{}

Question: I want to get the daily/monthly/yearly count of records based on dateCreated. Can I use QueryDSL for the group by here? It lets me create groupBy clause like below:

GroupBy.groupBy(QEnterprise.enterprise.dateCreated.month());

I'm not sure how to use this GroupByBuilder to query repository now. Repository has a findAll method which takes com.querydsl.core.types.Predicates but none that takes com.querydsl.core.group.GroupByBuilder

Answer 1

You couldn't pass GroupBy clause in default implementation. To get your desired output, firstly, you have to implement custom repository, and secondly you have to write your own method and logics.

First step:

---

Write a new repository interface:

import org.springframework.data.repository.NoRepositoryBean;
import com.querydsl.core.types.Predicate;
import java.util.Map;

@NoRepositoryBean
public interface EnterpriseRepositoryCustom {
    Map<Integer, Long> countByMonth(Predicate predicate);
}

Now inherit this interface to your existing EnterpriseRepository:

public interface EnterpriseRepository extends 
            PagingAndSortingRepository<Enterprise, String>, 
            QuerydslPredicateExecutor<Enterprise>,
            EnterpriseRepositoryCustom{}

Then create new implementation class of custom repository:

import com.querydsl.core.group.GroupBy;
import com.querydsl.core.types.Predicate;
import org.springframework.data.jpa.repository.support.QueryDslRepositorySupport;
import org.springframework.stereotype.Repository;
import java.util.Map;

@Repository
public class EnterpriseRepositoryImpl extends QueryDslRepositorySupport 
             implements EnterpriseRepositoryCustom {
    public EnterpriseRepositoryImpl() {
        super(Enterprise.class);
    }

    @Override
    public Map<Integer, Long> countByMonth(Predicate predicate) {
        //Have to write logic here....
    }
}

Second step:

---

Write your logic in countByMonth method as follows:

public Map<Integer, Long> countByMonth(Predicate predicate) {
        Map<Integer, Long> countByMonth = getQuerydsl()
                .createQuery()
                .from(QEnterprise.enterprise)
                .where(predicate)
                .transform(GroupBy
                      .groupBy(QEnterprise.enterprise.dateCreated.month())
                      .as(QEnterprise.enterprise.count()));
       return countByMonth;
    }

Optional: If you want to get list of records by monthly, then just modified count as -

        Map<Integer, List<Enterprise>> recordByMonth = getQuerydsl()
                .createQuery()
                .from(QEnterprise.enterprise)
                .where(predicate)
                .transform(GroupBy
                      .groupBy(QEnterprise.enterprise.dateCreated.month())
                      .as(GroupBy.list(QEnterprise.enterprise)));

I hope, you found your answer!!

Sample github project.