0

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%'

0 Answers0