# Avoiding unnecessary Join with Many-to-many relation

**URL:** <https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465>\
**Category:** Hibernate ORM\
**Created:** [March 30, 2018, 11:21pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465 "2018-03-30T23:21:57Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![diesieben07](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/diesieben07/32/114_2.png) [@diesieben07](https://discourse.hibernate.org/u/diesieben07)\
**Post date:** [March 30, 2018, 11:21pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/1 "2018-03-30T23:21:57Z")

</div>

I have two entities, Article and Category, every article can have multiple categories, so this is a classic many-to-many relationship.

My code looks like this (written in Kotlin, but should not be relevant):

```auto
@Entity(name = "ArticleEntity")
@Table(name = "article", uniqueConstraints = [
    UniqueConstraint(name = ArticleEntity.SLUG_KEY_NAME, columnNames = ["slug"])
])
class ArticleEntity(
    @Id
    @GeneratedValue
    var id: UUID? = null,

    @ManyToMany
    @JoinTable(
        name = "article_categories",
        joinColumns = [JoinColumn(name = "article_id", foreignKey = ForeignKey(name = "article_categories_article_id_fk"))],
        inverseJoinColumns = [JoinColumn(name = "category_id", foreignKey = ForeignKey(name = "article_categories_category_id_fk"))]
    )
    var categories: MutableSet<CategoryEntity>?
)

@Entity
@Table(name = "category")
class CategoryEntity(
    @Id
    @GeneratedValue
    val id: UUID?,

    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "categories")
    val articles: Set<ArticleEntity>?
)

```

I am trying to select only articles, which are in a list of categories (i.e. “give me only articles in categories a, b and c”).  
I turned to the JPA CriteriaQuery API (through Spring Specifications, not really relevant either):

```auto
override fun createPredicates(predicates: MutableList<Predicate>, root: Root<ArticleEntity>, query: CriteriaQuery<*>, cb: CriteriaBuilder) {
    val category = listOf(categoryId1, categoryId2, ...)
    val sq = query.subquery(Int::class.java).select(cb.literal(1))
    val sqRoot = sq.correlate(root)
    val sqJoin = sqRoot.join<ArticleEntity, CategoryEntity>("categories")
    sq.where(
        sqJoin.get<UUID>("id").`in`(category)
    )

    predicates += cb.exists(sq)
}

```

This should only generate an inner join of the JoinTable (`article_categories`), since my where condition is only based on the ID (which is present in the JoinTable). However Hibernate generates _two_ joins, also joining the entire `category` table:

```auto
select
  articleent0_.id as id1_0_,
from article articleent0_
where exists(select 1
             from article_categories categories1_, category categoryen2_
             where articleent0_.id = categories1_.article_id and categories1_.category_id = categoryen2_.id and (categories1_.id in (?)))
limit ?;

```

Is there a way to avoid this unnecessary join? I assume it does not matter _too_ much, but I’d like to avoid it if possible. Curiously, it does not happen if I perform a similar query on a many-to-one relation.

Thanks for any insights.

---

<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:** [March 31, 2018, 5:33am UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/2 "2018-03-31T05:33:01Z")

</div>

It’s because you explicitly told Hibernate to do a join:

```
val sqJoin = sqRoot.join<ArticleEntity, CategoryEntity>("categories")
```

---

<div class="post-metadata">

**Author:** ![diesieben07](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/diesieben07/32/114_2.png) [@diesieben07](https://discourse.hibernate.org/u/diesieben07)\
**Post date:** [March 31, 2018, 7:52pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/3 "2018-03-31T19:52:54Z")

</div>

Well, okay, touché, I should have worded my question better.  
How can I avoid this join then? I only need it to access the ID column, but that already exists in the join table, but I can’t access the column in it (or can I?).

---

<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:** [March 31, 2018, 8:25pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/4 "2018-03-31T20:25:18Z")

</div>

The default many-to-many association hides the intermediary table, so I’m not sure if you can reference it without joining the Category.

You can avoid that extra join by mapping the intermediary table as an entity: `CategoryArticle`. [This article explains how to do that](https://vladmihalcea.com/the-best-way-to-map-a-many-to-many-association-with-extra-columns-when-using-jpa-and-hibernate/).

Then, you query will be as easy as this:

```auto
select a
from Article a
join a.categoryArticles ca
where ca.category.id in :categoryIds

```

This should avoid the extra join to `Category`.

---

<div class="post-metadata">

**Author:** ![diesieben07](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/diesieben07/32/114_2.png) [@diesieben07](https://discourse.hibernate.org/u/diesieben07)\
**Post date:** [March 31, 2018, 9:21pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/5 "2018-03-31T21:21:05Z")

</div>

Thanks, I see.  
Are there any performance implications when switching from a @ManyToMany to this “manual many-to-many”?

---

<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:** [March 31, 2018, 9:36pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/6 "2018-03-31T21:36:26Z")

</div>

For Lists, you will gain [better performance](https://vladmihalcea.com/the-best-way-to-use-the-manytomany-annotation-with-jpa-and-hibernate/). For Sets, there’s no difference.

---

<div class="post-metadata">

**Author:** ![diesieben07](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/diesieben07/32/114_2.png) [@diesieben07](https://discourse.hibernate.org/u/diesieben07)\
**Post date:** [March 31, 2018, 9:56pm UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/7 "2018-03-31T21:56:31Z")

</div>

Thanks again, will definitely keep an eye on your blog 🙂  
I was concerned, since Hibernate now needs to manage an additional entity type, but that probably falls into the category of premature optimization.  
Case closed! 🙂

---

<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:** [April 1, 2018, 6:56am UTC](https://discourse.hibernate.org/t/avoiding-unnecessary-join-with-many-to-many-relation/465/8 "2018-04-01T06:56:19Z")

</div>

That’s not a problem. It always boils down to what SQL statements are generated.