SecondaryTables to allow join multiple tables on non-primary key

[joedevgee]

Currently @SecondaryTables only support join multiple tables with the same primary key. But in reality we often need the ability to join multiple tables using nested structure based on non-primary key

Take for example the below SQL

select a.col_a_primary_key,
           a.col_a_1,
           d.col_d_1,
           d.col_d_2,
           d.col_d_3
from a,
         b,
         c,
         d
where a.col_a_primary_key = 1,
and b.col_b_primary_key = a.col_a_some_non_primary,
and c.col_c.primary_key = b.col_b_some_non_primary,
and d.col_d_primary_key = c.col_c_some_non_primary

Without the ability to use non-primary key in @SecondaryTables, I think the current solution is to write @Entity for b and c and d and use OneToOne in each of them

However, if we can use non-primary in @SecondaryTables, it would be much simpler. Something along the lines of

@Entity
@SecondaryTables({
    @SecondaryTable(name = b, JoinColumns = {@KeyJoinColumn(name ='col_b_primary_key', reference='col_a_some_non_primary')}),
    @SecondaryTable(name = c, JoinColumns = {@KeyJoinColumn(name ='col_c_primary_key', reference='col_b_some_non_primary')}),
     @SecondaryTable(name = d, JoinColumns = {@KeyJoinColumn(name ='col_d_primary_key', reference='col_c_some_non_primary')}),
})
class a {
   @Id
   @Column(name='col_a_primary_key')
   public Long aId;

   @Column(name='col_b_some_non_primary', table ='b')
   public Long bNonePId;

   @Column(name='col_c_primary_key', table = 'c')
    public Long cId;
    
   @Column(name='col_c_non_primary_key', table = 'c')
    public Long cNonPId;
   
   @Column(name='col_d_1', table = 'd')
    public String d1;

    @Column(name='col_d_2', table = 'd')
    public String d2;

    @Column(name='col_d_3', table = 'd')
    public String d3;
}

[beikov]

The SecondaryTable annotation comes from JPA, so there is no way for Hibernate to change it. So if we want to support something like this, we would have to introduce a new Hibernate annotation for this purpose. To be honest though, I'm not sure this is really worth the trouble.

You write non primary key, but I guess you mean understand that this must still be at least a natural id which is unique, right? From what I can see in PrimaryKeyJoinColumn, we would at least already be able to spell this relationship, but I doubt that we can easily implement this internally right now. Behind the scenes, we usually have column name arrays that match the order of the primary key column names, so that we internally only need to iterate once and render c[i] = pk[i]. If we would want to support anything else here, we'd need some different structure to represent these key columns.

Overall, I'm not against the idea, but it sure is a lot of work and I doubt that there is anyone else out that requiring this feature, so I'd rather work on something that reaches more people. If anyone else wants to work on this though, I'd gladly guide that person.