# How to use the Hibernate @JoinFormula annotation with a @OneToMany JPA association?

**URL:** <https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461>\
**Category:** Hibernate ORM\
**Created:** [September 27, 2018, 6:18pm UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461 "2018-09-27T18:18:13Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Wukash](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/wukash/32/746_2.png) [@Wukash](https://discourse.hibernate.org/u/Wukash)\
**Post date:** [September 27, 2018, 6:18pm UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/1 "2018-09-27T18:18:13Z")

</div>

Suppose I have the following entity:

```auto
@Entity
@Table(name = "invoice")
public class Invoice implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator", allocationSize = 1000)
    private Long id;

    @Column(name = "hierarchy_path")
    private String hierarchyPath;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinFormula(value = "hierarchy_path like id || '/_%'", referencedColumnName = "hierarchy_path")
    private Set<Invoice> childInvoices;
}

```

And following data in table invoice:

```auto
id hierarchy_path
5325000 5325000/
5326000 5325000/5326000/
5327000 5325000/5326000/5327000/

```

i.e. every invoice can be corrected by another invoice (and correction can be corrected as well).

I would like childInvoices to contain the set of all corrections of given invoice. However such mapping fails during application startup with exception

```auto
java.lang.ClassCastException: org.hibernate.mapping.Formula cannot be cast to org.hibernate.mapping.Column

```

I tried many other ways to map it but I cannot get this to work. Please explain why it doesn’t work and if there actually is a way to accomplish that.

The resulting select should like this:

```auto
select invoice0_.id as id1_8_0_,
       invoice0_.hierarchy_path as hierarchy_path4_8_0_,
       invoice1_.id as id1_8_1_,
       invoice1_.hierarchy_path as hierarchy_path4_8_1_
from invoice invoice0_ 
left outer join invoice invoice1_ on invoice1_.HIERARCHY_PATH like invoice0_.ID || '/_%';

```

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [September 27, 2018, 6:59pm UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/2 "2018-09-27T18:59:26Z")

</div>

As explained in [this article](https://vladmihalcea.com/how-to-customize-an-entity-association-join-on-clause-with-hibernate-joinformula/), move the `@JoinFormula` to the `@ManyToOne` side and make the `@OneToMany` bidirectional.

A bidirectional association is also [much more efficient than a unidirectional one](https://vladmihalcea.com/the-best-way-to-map-a-onetomany-association-with-jpa-and-hibernate/).

---

<div class="post-metadata">

**Author:** ![Wukash](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/wukash/32/746_2.png) [@Wukash](https://discourse.hibernate.org/u/Wukash)\
**Post date:** [September 27, 2018, 8:38pm UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/3 "2018-09-27T20:38:51Z")

</div>

I tried this:

```auto
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinFormula(value = "SUBSTR(hierarchy_path, 0, INSTR(hierarchy_path, '/') - 1)", referencedColumnName = "id")
    private Invoice rootInvoice;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "rootInvoice")
    private Set<Invoice> childInvoices;

```

but it fails with the same exception. It works if I remove OneToMany completely and leave only ManyToOne.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [September 28, 2018, 3:12am UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/4 "2018-09-28T03:12:58Z")

</div>

Send a replicating test case as a Pull Request as explained in this article:

[http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/](http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/)

I’ll take a look on it afterward.

---

<div class="post-metadata">

**Author:** ![Wukash](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/wukash/32/746_2.png) [@Wukash](https://discourse.hibernate.org/u/Wukash)\
**Post date:** [September 28, 2018, 11:09am UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/5 "2018-09-28T11:09:41Z")

</div>

I added issue [https://hibernate.atlassian.net/browse/HHH-12997](https://hibernate.atlassian.net/browse/HHH-12997).

---

<div class="post-metadata">

**Author:** ![Carlos\_Lopez](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/carlos_lopez/32/812_2.png) [@Carlos\_Lopez](https://discourse.hibernate.org/u/Carlos_Lopez)\
**Post date:** [October 7, 2019, 10:47am UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/6 "2019-10-07T10:47:38Z")

</div>

It is happening the same to me. I’m just trying to retrieve a list from a JoinFormula but it just seems impossible.

---

<div class="post-metadata">

**Author:** ![alessiostalla](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/alessiostalla/32/882_2.png) [@alessiostalla](https://discourse.hibernate.org/u/alessiostalla)\
**Post date:** [December 9, 2019, 3:52pm UTC](https://discourse.hibernate.org/t/how-to-use-the-hibernate-joinformula-annotation-with-a-onetomany-jpa-association/1461/7 "2019-12-09T15:52:21Z")

</div>

Same here. I’m interested in this feature and I’m willing to contribute to implement it.