2
"The numbers at the beginning of the consultations and the "criterias" were inserted to facilitate the explanation. (1-...)(2-...)(3-...)"
I need to do this on JPA criteria:
SELECT DISTINCT service.*
FROM service AS service
INNER JOIN service_item AS serviceItem ON serviceItem.fk_service = service.id
INNER JOIN follow_up AS followUp ON followUp.fk_service = service.id
WHERE service.fk_branch = 241 AND service.fk_employee = 403
AND service.tenant_id = 246 AND customer.status = 'PROSPECT'
AND service.see_in BETWEEN '2010-01-01' AND '2018-12-31'
1-AND (SELECT DATEDIFF(MIN(serviceItem.arrival_date), MAX(serviceItem.departure_date))) = 6
2-AND (SELECT COUNT(followUp.id)) > 0
3-AND (SELECT MAX(followUp.date)) BETWEEN '201-01-01' AND '2018-12-31';
The query was created with criteria like this:
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Service> criteriaQuery = criteriaBuilder.createQuery(Service.class);
Root<Service> from = criteriaQuery.from(Service.class);
CriteriaQuery<Service> select = criteriaQuery.select(from).distinct(Boolean.TRUE);
1-Subquery<Long> subQueryDaysTraveling = select.subquery(Long.class);
Root<ServiceItem> subQueryFromDaysTraveling = subQueryDaysTraveling.from(ServiceItem.class);
subQueryDaysTraveling.select(criteriaBuilder.function("DATEDIFF", Long.class, criteriaBuilder.least(subQueryFromDaysTraveling.<Date>get("arrivalDate")),
criteriaBuilder.greatest(subQueryFromDaysTraveling.<Date>get("departureDate"))));
subQueryDaysTraveling.where(criteriaBuilder.equal(from.join("serviceItens"), subQueryFromDaysTraveling));
2-Subquery<Long> subQueryAmountFollowUp = select.subquery(Long.class);
Root<FollowUp> subQueryFromAmountFollowUp = subQueryAmountFollowUp.from(FollowUp.class);
subQueryAmountFollowUp.select(criteriaBuilder.count(subQueryFromAmountFollowUp));
subQueryAmountFollowUp.where(criteriaBuilder.equal(from.join("followUp"), subQueryFromAmountFollowUp));
3-Subquery<Date> subQueryLastFollowUp = select.subquery(Date.class);
Root<FollowUp> subQueryFromLastFollowUp = subQueryLastFollowUp.from(FollowUp.class);
subQueryLastFollowUp.select(criteriaBuilder.greatest(subQueryFromLastFollowUp.<Date>get("date")));
subQueryLastFollowUp.where(criteriaBuilder.equal(from.join("followUp"), subQueryFromLastFollowUp));
...
"Predicates simples, WHERE-ANDs"
...
1-predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subQueryDaysTraveling, daysTraveling)));
2-predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subQueryAmountFollowUp, amountFollowUp)));
3-predicates.add(criteriaBuilder.and(criteriaBuilder.between(subQueryLastFollowUp, lastFollowUpInitialDate, lastFollowUpFinalDate)));
...
Restante da criteria
...
The 3 subquerys are in the same model, what differs is the comparison, the two that use "Equal" work the one that uses "between" not and generates the following error:
Java.lang.ClassCastException: org.eclipse.persistence.internal.jpa.querydef.SubQueryImpl cannot be cast to org.eclipse.persistence.internal.jpa.querydef.ExpressionImpl
If I change predicate 3 to the following line, the error no longer occurs:
predicates.add(criteriaBuilder.between(subQueryFromLastFollowUp.<Date>get("date"), lastFollowUpInitialDate, lastFollowUpFinalDate));
This is the result in the generated query log. Subquery 1 and 2 perform the a select and compare with the table "serviceItem" the third represented by T5 only the between
1-AND ((SELECT DATEDIFF(MIN(t6.arrival_date), MAX(t6.departure_date)) FROM service_item t6 WHERE ((t6.id_service_item = serviceItem.id_service_item) AND (t6.tenant_id = ?))) = ?))
2-AND ((SELECT COUNT(t7.id) FROM follow_up t7 WHERE ((t7.id = followUp.id) AND (t7.tenant_id = ?))) = ?))
3-AND (t5.date BETWEEN ? AND ?))