I have the following type of query that I wish to convert into a jpa criteria query and the following table structure:
Table A 1-->1 Table B 1<--* Table C (proceedings) *-->1 Table D(prcoeedingsstatus)
-------- -------- ------- -------
aID bID cID dID
... .... timestamp textValue
f_bID .... f_bID
f_dID
1 A has 1 B, 1 B has many proceedings and each proceeding has a proceedingstatus.
SELECT a.*
FROM ((a LEFT JOIN b ON a.f_b = b.id)
LEFT JOIN proceedings ON b.id = proceedings.f_b)
RIGHT JOIN proceedingsstatus ON proceedings.f_d = proceedingsstatus.id
WHERE d.textValue IN ("some unique text")
AND c.timestamp BETWEEN 'somedate' AND 'anotherdate'
When I now try to do something like this for the predicates:
Predicate conditions = (root.join("tableB")
.joinList("proceedings")
.join("proceedingsstatus").get("textValue"))
.in(constraintList.getSelectedValues());
Predicate time = cb.between((root.join("tableB")
.joinList("proceedings")
.<Date>get("timestamp")), dt1.toDate(), dt2.toDate());
constraints = cb.and(conditions, time);
Right now it selects entries A where there is at least 1 occurrence of the right proceedingsstatus according to the conditions-predicate if in any of A's proceedings the 'timestamp' matches the time-predicate that I built. So it would also select an entry A when C.timestamp is correct for a proceeding with the wrong textValue in D, if there is at least one entry C belonging to A with the right textvalue in D.
How can I change it so that it only selects A's where the proceedingsstatus has the right value AND the time of proceeds is correct?
