I have a train service application
I have a class called Booking, it also has Product as a list on it.
Product has TrainBooking and TrainBooking has TrainJourney as a list on it.
I use the criteria builder to fetch bookings.
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Booking> bookingRoot = query.from(Booking.class);
initializeJoins(bookingRoot);
Order orderParam = getOrderParam(request, cb, bookingRoot);
Fetch<Booking, Product> productFetch = bookingRoot.fetch("products", JoinType.LEFT);
Fetch<Product, TrainBooking> trainBookingFetch = productFetch.fetch("trainBooking", JoinType.LEFT);
productFetch.fetch("provider", JoinType.LEFT);
Fetch<TrainBooking, TrainJourney> trainJourneyFetch = trainBookingFetch.fetch("trainJourneys", JoinType.LEFT);
trainJourneyFetch.fetch("journey", JoinType.LEFT);
query.multiselect(bookingRoot, journeyJoin.get(DEP_DATE_STR))
.orderBy(orderParam)
.distinct(true)
.where(createPredicates(request, otaUser, cb, bookingRoot));
I use the distinct keyword here but it fetches some duplicate bookings. I want to use groupBy by bookingId and order by departure date. I could not find any way to use both order by and group by in the criteria builder for my case. Do you know how I can handle this?