@OneToMany / @ManyToMany through array column

Hibernate ORM
1

Hi mates!

I can’t believe there’s no literature of this usecase in Internet, but considering a db table like

Company
-------
id UUID
door_key_owners UUID[]

Employee
--------
id UUID
company UUID (FK)

How can someone define an entity like

@Entity
@Table(name = "company")
public class Company {
  @ManyToMany(fetch = EAGER) // Or OneToMany
  @JoinFormula("id = ANY(door_key_owners)")
  List<Employee> doorKeyOwners;
}

I know it’s not a proper desig nor effiicientn, but I am not allowed to change the schema as it comes imposed.

I tried with almost everything OneToMany, ManyToMany, Fomula, JoinFormula, ElementCollection
Is there any way to support this mapping?

Thanks!

2

We have in fact thought about this already and you can track HHH-18114 to get notified about progress on this. In the meantime, you could try a mapping similar to this:

@Entity
@Table(name = "company")
public class Company {
  @ManyToMany(fetch = EAGER)
  @JoinTable(name = "(select company.id as company_id, t.employee_id from unnest(door_key_owners) t(employee_id))",
    joinColumns = @JoinColumn(name = "company_id", insertable = false, updatable = false),
    inverseJoinColumns = @JoinColumn(name = "employee_id", insertable = false, updatable = false))
  List<Employee> doorKeyOwners;
}

I don’t know if it will work, but alternatively, you can try to achieve this through defining a dedicated @Subselect entity for the array:

@Entity
@Table(name = "company")
public class Company {
  @OneToMany(mappedBy = "company")
  List<CompanyKeyOwner> doorKeyOwners;
}

@Entity
@Subselect("select c.id as company_id, t.employee_id from company c join lateral unnest(c.door_key_owners) t(employee_id)")
public class CompanyKeyOwner {
  @Id
  @ManyToOne
  @JoinColumn(name = "company_id")
  Company company;
  @Id
  @ManyToOne
  @JoinColumn(name = "employee_id")
  Employee employee;
}

Or query it manually in HQL if you don’t need the association in the entity model:

@Entity
@Table(name = "company")
public class Company {
  @Column(name = "door_key_owners")
  List<UUID> doorKeyOwners;
}

select c, e from Company c 
left join lateral unnest(c.doorKeyOwners) doorKeyOwnerId
left join Employee e on e.id = doorKeyOwnerId