Spring Boot: How to create dynamic query involving 'join' and 'group by' using predicate

Question

My post request body will be like

{
    "queryCondition":[
            {
                "filter":"status",
                "filterlist":["Closed","New","Resolved"...]
            },
            {
                "filter":"assigned_team",
                "filterlist":["A","B","C"...]
            },
            {
                "filter":"assigned_to",
                "filterlist":["ram","govind","ajith"...]
            },
            {
                "filter":"duration",
                "filterlist":["2020-02-01","2020-05-01"....]
            }
            ....
            ....
        ],
    "durationField":"created_date"
}

I receive the columns(filter) and values(filterlist) dynamically with which I need to build this query.

SELECT * FROM tickets 
WHERE ticket_id IN (SELECT ticket_id FROM Tickets WHERE created_date >= '2020-02-01') AND created_date '2020-05-01'
AND status IN ('Closed','Resolved','New')
AND assigned_team IN ('A' , 'B', 'C')
AND assigned_to IN ('ram','govind','ajith');

I built this query dynamically using Predicate and it is working fine.

@Override
public List<Tickets> conditionedQuery(QueryCondition queryCondition) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tickets> query = cb.createQuery(Tickets.class);
    Root<Tickets> ticket = query.from(Tickets.class);
    List<Predicate> predicatessub = new ArrayList<>();
    for(FilterConditions fc:queryCondition.getQueryCondition()) {
        if(fc.getFilter().equals("duration")) {
            Predicate ps = cb.greaterThanOrEqualTo(ticket.get(queryCondition.getDurationField()), fc.getFilterlist()[0]);
            Predicate pe = cb.lessThan(ticket.get(queryCondition.getDurationField()), fc.getFilterlist()[1]);
            predicatessub.add(cb.and(ps,pe));
        }else 
        {
            List<Predicate> predicates = new ArrayList<>();
            for(int i=0; i<fc.getFilterlist().length; i++) {
                Predicate p = cb.equal(ticket.get(fc.getFilter()),fc.getFilterlist()[i]);
                predicates.add(p);
            }
            predicatessub.add(cb.or(predicates.toArray(new Predicate[predicates.size()])));
        }
    }
    query.select(ticket)
    .where(cb.and(predicatessub.toArray(new Predicate[predicatessub.size()])));
    return entityManager.createQuery(query)
    .getResultList();
}

QueryCondition.class

public class QueryCondition {
    private List<FilterConditions> filterCondition;
    private String durationField;
}

FilterConditions.class

public class FilterConditions {
    private String filter;
    private String[] filterlist;
}

Now I would like to build a quite more complex query involving joins and group by. Below is the sample query like what I would like to build using predicate.

SELECT 
YEAR(pt.created_date),
MONTH(pt.created_date),
pt.assigned_team,
COUNT(tk.ticket_id)
FROM
(SELECT 
    *
FROM
    tickets
WHERE
    ticket_id IN (SELECT 
            ticket_id
        FROM
            Tickets
        WHERE
            resolved_date >= '2020-02-01')
        AND resolved_date < '2020-05-01'
        and assigned_team IN ('A' , 'B', 'C')) pt
    LEFT JOIN
(SELECT 
    *
FROM
    tickets
WHERE
    status IN ('Closed','Resolved','New')
        AND assigned_to IN ('ram','govind','ajith')) tk ON tk.ticket_id = pt.ticket_id
GROUP BY YEAR(pt.created_date) , MONTH(pt.created_date), pt.assigned_team order by 
pt.assigned_team,YEAR(pt.created_date),MONTH(pt.created_date) asc;

Kindly advise how this can be achieved with Predicate or is there any other simpler way than Predicate.

Answer 1

Maybe this works:

First create a list with all field your model but removing associations. After pass this list in CriteriaQuery.

List<Expression<?>> groupByList = new ArrayList<>();

// ticket => Root<Tickets>
ticket.getModel().getAttibutes().stream()
    .filter(a -> !a.isAssociation())
    .forEach(a -> groupByList.add(ticket.get(a.getName())));

query.select(ticket)
    .where(cb.and(predicatessub.toArray(new Predicate[predicatessub.size()])))
    .groupBy(groupByList); // <== add group by

If you have join just do the same thing creating a cast with object EntityTypeImpl<?>. ex:

// import org.hibernate.metamodel.model.domain.internal.EntityTypeImpl
// join => Join<?,?>
((EntityTypeImpl<MyEntity>) join.getModel())
    .getDeclaredAttributes().stream()
    .filter(a -> !a.isAssociation())
    .forEach(a -> groupByList.add(join.get(a.getName())));