Left join not retrive nulls
I need to get all records A when A.B.name like "%pac%" OR A.C.name like "%pac%"
Class<T> entityClass = (Class<T>) A.class;
PathBuilder<T> entity = new PathBuilder<T>(entityClass, "entity");
EntityManager entityManager = entityManagerProvider
.getEntityManager(entity.getType());
JPAQuery query = new JPAQuery(entityManager);
query = query.from(entity);
PathBuilder<?> associationPathB = entity.get("B");
query = query.leftJoin(associationPathB).fetch();
PathBuilder<?> associationPathC = entity.get("C");
query = query.leftJoin(associationPathC).fetch();
BooleanBuilder baseSearch = new BooleanBuilder();
baseSearch.and(associationPathB.getString("name").lower().like("%pac%"))
.or(associationPathC.getString("name").lower().like("%pac%"));
query.where(baseSearch.getValue());
long count = query.count();
the generated query is
select entity from A entity
left join fetch entity.B
left join fetch entity.C
where lower(entity.B.name) like ?1 escape '!'
or lower(entity.C.name) like ?2 escape '!'
count return only 2 records but I have 10 records of type A with B.name like='pac'.
Only 2 records are not null in column C, but this is left join with OR condition
Why not return the 10 records?
if I execute the query in the DB it returns the 10 registers
select * from A entA
left join B entB on entB.id = entA.entB
left join C entC on entC.id = entA.entC
where entB.name like '%pac%' OR
entC.name like '%pac%'