CriteriaAPI - GroupBy - Not Generated

I'm tearing my hair out over something that may very well be very simple, but I just cant get it right.

My GroupBy clause is not being added to the SQL generated by EclipseLink.

Have tried many different orders and variations of the code below.

public List<Orders> findOrdersEntitiesBySearch(int maxResults, int firstResult, String column1, String column2, String key, boolean searchOrder) {
    EntityManager em = getEntityManager();
    try {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Orders> cq = cb.createQuery(Orders.class);
        Root<Orders> order = cq.from开发者_StackOverflow社区(Orders.class);
        Join<Orders, Products> prod = order.join("productsCollection");

        // Where like key
        if (column1 != null && column2 != null) {
            if (searchOrder) {
                cq.where(cb.or(cb.like(cb.lower(order.get(column1).as(String.class)), "%" + key.toLowerCase() + "%"), cb.like(cb.lower(order.get(column2).as(String.class)), "%" + key.toLowerCase() + "%")));
            } else {
                cq.where(cb.or(cb.like(cb.lower(prod.get(column1).as(String.class)), "%" + key.toLowerCase() + "%"), cb.like(cb.lower(prod.get(column2).as(String.class)), "%" + key.toLowerCase() + "%")));
            }
        } else {
            if (searchOrder) {
                cq.where(cb.like(cb.lower(order.get(column1).as(String.class)), "%" + key.toLowerCase() + "%"));
            } else {
                cq.where(cb.like(cb.lower(prod.get(column1).as(String.class)), "%" + key.toLowerCase() + "%"));
            }
        }

        // Order By
        List<Order> orderByList = new ArrayList<Order>();
        orderByList.add(cb.desc(order.get("ordDate")));
        orderByList.add(cb.desc(order.get("pkOrdID")));
        cq.orderBy(orderByList);

        // Select
        cq.select(order);

        // Group by
        //cq.groupBy(order.get("pkOrdID"));
        //Expression<Integer> grouping = order.get("pkOrdID").as(Integer.class);
        Expression<String> grouping = order.get("pkOrdID").as(String.class);
        cq.groupBy(grouping);

        Query q = em.createQuery(cq);
        q.setMaxResults(maxResults);
        q.setFirstResult(firstResult);

        return q.getResultList();
    } finally {
        em.close();
    }
}

The code compiles an runs fine, I get results but my GroupBy clause is not included.

As a nasty quickfix, I am running the list returned through a function to remove the duplicates until I can find the solution.

Thanks in advance for any assistance,

David

---

For clarity, re-written as regular JPQL query, you currently have something like this:

SELECT o
    FROM Orders o JOIN o.productsCollection p
    WHERE ...
    GROUP BY o.pkOrdID...

There are two issues here. First, the group by is not correct, because you can't group by on a single column when a full object is selected - just as with standard SQL, all selected columns that are not aggregates must be listed in the group by. The second issue is that you don't need group by here at all. See below for your options:

Since you don't use any aggregate functions here, what you actually want is simply:

SELECT DISTINCT o
    FROM Orders o JOIN o.productsCollection p
    WHERE ...

Therefore, simply drop the group-by from your criteria API query, and use cq.distinct(true) instead.

If you really need group by with aggregate functions for a different query, instead of grouping on the primary key of a selected object, in JPA you group by the object itself. A simple JPQL example might be:

SELECT o, sum(p.quantity)
    FROM Orders o JOIN o.productsCollection p
    WHERE ...
    GROUP BY o

In your query, this would be cq.groupBy(order).

Btw. I have no idea why eclipse link simply ignores your group by here instead of reporting an error. Which version are you using?