Spring boot JPA - EntityGraph - cross join to left outer join

Question

I have some classes, performing some queries with Entity Graph. The code is generating a cross join ... that I would like to change to left outer join.

Is this possible ?

So I got a this Classes

@Table(schema = "public", name = "parent")
public class Parent implements Serializable {

@Id
private Long id;

@Column(name = "created_at", updatable = false)
private LocalDateTime createdAt;

@ManyToOne
@JoinColumn(name = "child1")
private Child1 child1;

@ManyToOne
@JoinColumn(name = "child2")
private Chil child2;

@ManyToOne
@JoinColumn(name = "child3")
private Child3 child3;

@OneToOne(mappedBy = "Parent")
private Child4 child4

}

....

@Entity
@Table(schema = "public", name = "child4")
public class Child4 implements Serializable {

@Id
private Long parentId;

private boolean printed;

private boolean read;

private boolean done;

@OneToOne
@PrimaryKeyJoinColumn(name = "parent_id", referencedColumnName = "parent_id")
private Parent parent;
}

then I got this repository

@Repository
public interface ParentRepository extends JpaRepository<Parent, Long>, 
QuerydslPredicateExecutor<Parent> {

@EntityGraph(
        type = EntityGraph.EntityGraphType.FETCH,
        attributePaths = {
                "child1",
                "child2",
                "child3",
                "child4"
        }
)
Page<Interrogation> findAll(Predicate predicate, Pageable pageable);

}

and this is SQL result

select 
.....
from
public.parent parent0_ 
left outer join
  public.child1 child1_ 
  on parent0_.child1_id = child1_.id 
left outer join
  public.child2 child2_ 
  on parent0_.child2_id = child2_.id 
left outer join
  public.device child3_ 
  on parent0_.child3_id = child3_.id 
cross join
  child4 child4_ 
where
  parent.id = child4_.parent_id 

My issue that I'd like to change the cross join with a left join ...

is that possible ?

Answer 1

I've got the similar problem with you, I have a com.querydsl.core.types.Predicate object and use Page<Interrogation> findAll(Predicate predicate, Pageable pageable); to get result.

but all @ManyToOne field with null value will be filtered. Since you are using QueryDsl, I offer the QueryDsl solution.

1. teardown pagable object for orderBy, offset and limit.
2. get the list, you can force using left join here

        List<?> result = queryFactory 
                .from(QParent.parent)  
                .leftJoin(QParent.parent.child1, QChild1.child1)
                .leftJoin(QParent.parent.child2, QChild1.child2)
                .leftJoin(QParent.parent.child3, QChild1.child3)
                .where(predicate)
                .orderBy(QParent.parent.createdAt.desc())
                .offset(2 * 20)
                .limit(20)
                .fetch();

the query generated like this:

... from parent parent0_ left outer join child1 child11_ on parent0_.child1_id=child11_.id where ...

2. assemble the Page object with helper methods.

        getPageFromJPAQuery(QParent.parent, 
               queryFactory 
                .from(QParent.parent)  
                .leftJoin(QParent.parent.child1, QChild1.child1)
                .leftJoin(QParent.parent.child2, QChild1.child2)
                .leftJoin(QParent.parent.child3, QChild1.child3)
                .where(predicate)
                .orderBy(QParent.parent.createdAt.desc()), 
               pageable
        );

    private <Q extends SimpleQuery<Q>> Page<?> getPageFromJPAQuery(EntityPathBase<?> path, JPAQuery<?> where, Pageable pageable) {
//have to clone JPAQuery
        List<?> result = where.clone()
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();
        // fetch the count from another query
        Long count = where.select(path.count()).fetchOne();

        return getPageFromList(result, pageable, count);
    }

    public static <T> Page<T> getPageFromList(List<T> list, Pageable pageable, Long count) {
        int pageNumber = pageable.getPageNumber();
        int pageSize = pageable.getPageSize();

        return new PageImpl<>(list, PageRequest.of(pageNumber, pageSize), count);
    }

searching for a JPA solution...