I have a parent-child-grandchild relationship and I'm trying to fetch it in a single sql query.

The Parent has two OneToOne Mappings to the Child, each Child has a OneToMany Association to the Grandchildren:

public class Parent {
    @OneToOne
    @JoinColumn(name = "child_id")
    @Fetch(FetchMode.JOIN)
    private Child child;

    @OneToOne
    @JoinColumn(name = "otherchild_id")
    @Fetch(FetchMode.JOIN)
    private Child otherchild;
}

public class Child {
    @OneToMany(mappedBy = "child")
    @Fetch(FetchMode.JOIN)
    private Set<Grandchild> grandchildren = new HashSet<>();
}

public class Grandchild {
    @ManyToOne
    private Child child;
}

With this Mapping I got two sql queries:

    select
        parent0_.parent_id as parent_i1_121_0_,
        parent0_.child_id as child_id2_121_0_,
        parent0_.otherchild_id as otherchi3_121_0_,
        child1_.child_id as child_id1_21_1_,
        grandchild2_.child_id as child_id2_65_2_,
        grandchild2_.grandchild_id as grandchi1_65_2_,
        grandchild2_.grandchild_id as grandchi1_65_3_,
        grandchild2_.child_id as child_id2_65_3_,
        child3_.child_id as child_id1_21_4_ 
    from
        Parent parent0_ 
    left outer join
        Child child1_ 
            on parent0_.child_id=child1_.child_id 
    left outer join
        Grandchild grandchild2_ 
            on child1_.child_id=grandchild2_.child_id 
    left outer join
        Child child3_ 
            on parent0_.otherchild_id=child3_.child_id 
    where
        parent0_.parent_id=?

    select
        grandchild0_.child_id as child_id2_65_0_,
        grandchild0_.grandchild_id as grandchi1_65_0_,
        grandchild0_.grandchild_id as grandchi1_65_1_,
        grandchild0_.child_id as child_id2_65_1_ 
    from
        Grandchild grandchild0_ 
    where
        grandchild0_.child_id=?

There's only one left join to the grandchild table. The second Grandchild Association ist loaded by the second query.

Is it possible two avoid the second sql query?

With debugging I found the class MetamodelDrivenLoadPlanBuilder (org.hibernate.loader.plan.build.spi) doing buildRootEntityLoadPlan with the Object strategy (FetchStyleLoadPlanBuildingAssociationVisitationStrategy) and persister (SingleTableEntityPersister).

The persister object contains all associations, but the strategy object contains only one fetch for the child-grandchild association. Seems that the other fetch is dropped, maybe because Hibernate can't fnd a difference to the first child- grandchild fetch.