Hibernate: LEFT JOIN FETCH with multiple columns for the join

databasehibernatehqljpa

We would like to left outer join two tables in HQL using two columns for the join.

The second column for the join has no relevance at the logical level and doesn't restrict the result set in any way. It's the partition key and is used solely as an optimization to speed up the phyiscal data access.

In SQL it would be something like

select * 
from t1 
left outer join t2 
on t1.id = t2.parent_id 
and t1.partion_key = t2.partition_key

The following approaches that we tried didn't work:

  • We can do an outer join in HQL left join fetch and it's possible to specify an additional condition in the join using with but both can't be combined together. This limitation is explicitely mentioned in the documentation:
    "Fetch should also not be used together with impromptu with condition".

  • Using an additional where condition t1.partion_key = t2.partition_key doesn't work because it change the semantics of the query from an outer join to an inner join: when no data matches the condition doesn't hold and the row is ignored.

  • Using the oracle syntax t1.partion_key = t2.partition_key (+) also doesn't work since it results in a SQL query that mixes the ANSI und Oracle syntax.

  • We though about using a combined key but it's not really correct because at the logical level, the key is only id. We don't want to let the physical data modelling (partitioning) impact the logical model.

How can we express the desired query with HQL?

Best Answer

1) Since Hibernate 5.1 we can use 'join' on unrelated classes in HQL queries. In this case we can use this HQL query:

select 
    p as parent, 
    c as child 
from 
    Parent p 
    left join Child c on c.parentId = p.id and c.partitionKey = p.partitionKey

2) Another approach is to modify entities as following (adding two JoinColumns to children property in Parent entity and replacing parent 'many-to-one' relation to simple parentId property in Child entity):

@Entity
public class Parent {    
    @Id
    @GeneratedValue
    private Integer id;

    @Column(name = "partition_key")
    private Integer partitionKey;

    @OneToMany
    @JoinColumns({
            @JoinColumn(name = "parent_id", referencedColumnName = "id"),
            @JoinColumn(name = "partition_key", referencedColumnName = "partition_key")
    })
    private List<Child> children;
}

@Entity
public class Child {    
    @Id
    @GeneratedValue
    private Integer id;

    @Column(name = "partition_key")
    private Integer partitionKey;

    @Column(name = "parent_id")
    private Integer parentId;

    // @ManyToOne
    // private Parent parent;
}

Then we can use the following simple JPQL query: 

select distinct p as parent from Parent p left join fetch p.children c

Both queries are translated by Hibernate to like this SQL query:

select 
    p.id, 
    p.partition_key, 
    s.id, 
    s.parent_id, 
    s.partition_key 
from 
    parents p 
    left outer join children c on (c.parent_id=p.id and c.partition_key=p.partition_key)

Working demo is here.

Related Topic