# How can I do a JOIN FETCH in criteria api

**URL:** <https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846>\
**Category:** Hibernate ORM\
**Created:** [May 26, 2018, 3:44pm UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846 "2018-05-26T15:44:15Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![masber](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/masber/32/132_2.png) [@masber](https://discourse.hibernate.org/u/masber)\
**Post date:** [May 26, 2018, 3:44pm UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/1 "2018-05-26T15:44:15Z")

</div>

Dear hibernate community,

I am trying to translate the query below to criteria api.

`SELECT er from ereturn er JOIN FETCH product_item pi ON pi.ereturn_id = er.id WHERE pi.status = "RECEIVED"`

To something like this:

```auto
CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Ereturn> criteria = builder.createQuery( Ereturn.class );

Root<Ereturn> er = criteria.from(Ereturn.class);
Join<Ereturn, ProductItem> productItemJoin = er.join("productItems", JoinType.LEFT);
Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);

List<Predicate> predicates = new ArrayList<>();

predicates.add(builder.equal( productItemJoin.get( "status" ), "RECEIVED"));

criteria.where(
        builder.and(predicates.toArray(new Predicate[predicates.size()]))
);

List<Ereturn> ers = em.createQuery( criteria )
    .getResultList();

```

The problem is that hibernate generates this query:

```auto
select
ereturn0_.id as ...
...
productite6_.id as ...
...
from
ereturn ereturn0_ 
join
product_item productite1_ 
on ereturn0_.id = productite1_.ereturn 
join
product_item productite6_ 
on ereturn0_.id = productite6_.ereturn
where
productite1_.status='RECEIVED';

```

**QUESTION:** how could I tell hibernate to generate this query with only 1 join while fetching fields from both tables (ereturn and productItem)?

thank you very much

---

<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:** [May 27, 2018, 11:20am UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/2 "2018-05-27T11:20:59Z")

</div>

You didn’t tell if there is something not working with your example.

Did you try that example and it didn’t work?

---

<div class="post-metadata">

**Author:** ![masber](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/masber/32/132_2.png) [@masber](https://discourse.hibernate.org/u/masber)\
**Post date:** [May 27, 2018, 3:21pm UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/3 "2018-05-27T15:21:34Z")

</div>

Dear @vlad,

I have updated my question, I hope it is more clear now

thank you very much

---

<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:** [May 27, 2018, 4:01pm UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/4 "2018-05-27T16:01:49Z")

</div>

Just remove the `productItemJoin` and use the `productItemFetch` instead.

```auto
CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Ereturn> criteria = builder.createQuery( Ereturn.class );

Root<Ereturn> er = criteria.from(Ereturn.class);
Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);

List<Predicate> predicates = new ArrayList<>();

predicates.add(builder.equal( productItemFetch.get( "status" ), "RECEIVED"));

criteria.where(
        builder.and(predicates.toArray(new Predicate[predicates.size()]))
);

List<Ereturn> ers = em.createQuery( criteria )
    .getResultList();

```

---

<div class="post-metadata">

**Author:** ![masber](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/masber/32/132_2.png) [@masber](https://discourse.hibernate.org/u/masber)\
**Post date:** [May 27, 2018, 4:33pm UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/5 "2018-05-27T16:33:48Z")

</div>

I changed code to `predicates.add(builder.equal( productItemFetch.get( "status" ), "RECEIVED"));` but compiler complains:

> The method get(String) is undefined for the type Fetch\<Ereturn,ProductItem\>

I also tried casting from Fetch to Join like `Join<Ereturn, ProductItem> productItemFetch = (Join<Ereturn, ProductItem>)er.fetch("productItems", JoinType.LEFT);` but

> Cannot cast from Fetch to Join

I am using Hibernate 5.2.17.Final

---

<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:** [May 27, 2018, 4:55pm UTC](https://discourse.hibernate.org/t/how-can-i-do-a-join-fetch-in-criteria-api/846/6 "2018-05-27T16:55:40Z")

</div>

Just cast the `productItemFetch` to a `Join` and then use the `productItemJoin` to get the attribute.

```auto
CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Ereturn> criteria = builder.createQuery( Ereturn.class );

Root<Ereturn> er = criteria.from(Ereturn.class);
Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);
Join<Ereturn, ProductItem> productItemJoin = (Join<Ereturn, ProductItem>) productItemFetch;

List<Predicate> predicates = new ArrayList<>();

predicates.add(builder.equal( productItemJoin.get( "status" ), "RECEIVED"));

criteria.where(
        builder.and(predicates.toArray(new Predicate[predicates.size()]))
);

List<Ereturn> ers = em.createQuery( criteria )
    .getResultList();

```