This is a JPA Query question. I have successfully used "LEFT JOIN" and "IN:" in EntityManager.CreateQuery in separate situations, but when i combine the two e.g.
SELECT a.id
FROM TableA a
LEFT JOIN TableB b on a.myAField = b.id and b.myBField in :Ids
LEFT JOIN TableC c on b.myBOtherField = c.id
where Ids is List<Long>
it starts giving me this error "The left parenthesis is missing from the IN expression."
The sql works fine when pasted directly into postGres pgAdmin explicitly writing out the parameters. Have I made a mistake with the syntax here or is this a jpa limitation and i need an alternative?
List<Long> myBFieldTypes = new ArrayList<>();
myBFieldTypes.add(5L);
myBFieldTypes.add(10L);
try {
List<Long> resultArray = em.createQuery("SELECT a.id FROM TableA a LEFT JOIN TableB b on a.myAField = b.id and b.myBField in :Ids LEFT JOIN TableC c on b.myBOtherField = c.id")
.setParameter("Ids", myBFieldTypes)
.getResultList();
return resultArray;
} catch (Exception ex) {
throw ex;
} finally {
em.close();
}