JOIN not generated by Hibernate when first fetch-joining then simple-joining

Question

Given the following entities:

@Entity
public class User {

  @Id
  private Long id;

  private String name;

  @OneToMany(mappedBy = "user", fetch = LAZY)
  private Set<UserRole> userRoles = new HashSet<>();

}

@Entity
public class UserRole {

  @Id
  private Long id;

  private String name;

  @ManyToOne(fetch = LAZY)
  private User user;

  @ManyToMany(fetch = LAZY)
  private Set<Article> articles = new HashSet<>();

}

@Entity
public class Article {

  @Id
  private Long id;

  private String name;

  @ManyToMany(mappedBy = "articles", fetch = LAZY)
  private Set<UserRole> userRoles = new HashSet<>();

}

I'm building the following query:

From<?, ?> root = query.from(User.class);
From<?, ?> joinedUserRoles = (From<?, ?>) root.fetch("userRoles", JoinType.INNER);
From<?, ?> joinedArticles = joinedUserRoles.join("articles", JoinType.INNER);
query.where(joinedArticles.in(someArticles));

The idea is to fetch-join User#userRoles while simple-joining UserRole#articles. The problem is that the generated query is invalid:

select
    user0_.id as id1_6_0_,
    userroles1_.id as id1_7_1_,
    user0_.name as name5_6_0_,
    userroles1_.role as role2_7_1_,
    userroles1_.user_id as User_i3_7_1_,
    userroles1_.user_id as User_i3_7_0__,
    userroles1_.id as id1_7_0__ 
from
    User user0_ 
inner join
    UserRole userroles1_ 
        on user0_.id=userroles1_.user_id 
where
    generatedAlias2 in (
        ?
    )

Thrown error:

SQLSyntaxErrorException: Unknown column 'generatedAlias2' in 'where clause'

Indeed, JPA/Hibernate did not generated the simple-join UserRole#articles for some reason.

Any idea what's happening?

I'm on Hibernate 5.2.16.

Its not clear what your goal is. What is UserRole? Is it something like 'OriginalPoster' and 'Responder'? If so, why do you have a list of Articles there? Why not have a list of Articles in the User class? Further, why a list of UserRoles in an Article? How many UserRoles can there be? If only two, as mentioned, it doesn't make sense to have that list there. You really need to clarify your objective. — K.Nicholas, Apr 08 '18 at 19:28
@KarlNicholas Thanks for your comment. The entities have been thoroughly designed, all these relationships are the ones needed for our model. I don't really get what additional details I could give. Basically, the question is: how to simple-join after having fetch-joined. I'm not sure anything else is needed here. — sp00m, Apr 08 '18 at 20:35

Karol Dowbecki · Accepted Answer · 2018-04-08T18:09:11.843

2

As per Batch fetching - optimizing object graph loading:

JPQL does not allow the aliasing of the join fetch, so if you wish to also query on the relationship, you have to join it twice.

An example why this is prohibited is given in this answer.

Your casting allows to construct the criteria object however the provider doesn't seem to understand it properly and fails to resolve the articles alias.

Replacing the fetch() with join() might not be ideal in terms of loading lazy relationships but should construct a valid query.

edited Apr 08 '18 at 18:09

answered Apr 08 '18 at 17:22

Karol Dowbecki

43,645
9
78
111

Interesting links, thanks! I didn't know fetch-joins couldn't be aliased. I give an additional join a try tomorrow then, I'll come back to you. – sp00m Apr 08 '18 at 20:43

K.Nicholas · Answer 2 · 2018-04-11T18:01:34.687

0

Looked again: What's wrong with this? Seems to work.

CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> user = cq.from(User.class);
Join<User, UserRole> userRoles = user.join("userRoles", JoinType.INNER);
Join<UserRole, Article> articles = userRoles.join("articles", JoinType.INNER);
cq.where(articles.in(Arrays.asList(new Long[]{1L})));

edited Apr 11 '18 at 18:01

answered Apr 08 '18 at 22:15

K.Nicholas

10,956
4
46
66

JOIN not generated by Hibernate when first fetch-joining then simple-joining

2 Answers2