HQL left join with condition

Question

This may seem basic but it's late and I'm having trouble with the following.

class Group {

  @Id
  String id;
}

class Participation {

  @Id
  String id;

  @ManyToOne
  @JoinColumn(name = "GROUP_ID")
  Group group;

  @ManyToOne
  @JoinColumn(name = "USER_ID")
  User user;
}

class User {

  @Id
  String id;

  @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  Set<Participation> participations;
}

Class diagram

So

Participation -->1 Group

and User 1<-->N Participation

How can I retrieve all Groups with, for a given User, the associated Participation (or null is there is none)? I've been playing with join fetches but to no avail so far...

Many thanks,

CN

PS. I can do this in SQL thus :

select g.d, p.id 
from group as g 
left join participation as p 
on p.group_id = g.id and p.user_id = 2;

Answer 1

(Probably some typo in the HQL itself but the idea should be correct)

What you are asking, based on your SQL and description, is find out all Participation (and its corresponding Group) based on User, which is simply

select p.id, p.group.id from Participation p where p.user.id = :userId

To make it better, you should fetch the entities instead:

from Participation p left join fetch p.group where p.user.id = :userId

---

There were some confusions in understanding what you were trying to do: You want all groups (regardless of condition). And, for a given user, you want to find all groups and participations that user is involved.

Though it should be possible using Right-outer-join:

select g, p from Participation p 
    right outer join p.group g 
    where p.user.id=:userId

Or, in later version of Hibernate (>= 5.1 ?), it allow explicit join (haven't tried before, you may give it a try) (Replace with with on if you are using JPQL):

select g, p from Group g
    left outer join Participation p 
        with p.group = g
    left outer join p.user u
    where u.id = :userId

Or you may use other techniques such as subquery etc. However I would rather separate them into simpler queries in your case and do a simple aggregation in your code.

The basic idea is to have

1. Query for all groups: from Groups
2. Query for all participations for a user: from Participation p join fetch p.group where p.user.id=:userId

Then you can aggregate them easily to the form you want, e.g. Map<Group, List<Participation>>, or even more meaningful value object.

The benefit is the data access query is simpler and more reusable (esp if you are wrapping them in DAO/Repository finder method). One more round trip to DB shouldn't cause any obvious performance impact here.