RIGHT JOIN in JPQL

Question

I have the following JPA entities:

@Entity
class UserClient{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
}

@Entity
class UserAccess{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne(optional = false, cascade = { CascadeType.REFRESH })
    private UserClient user;

    @Temporal(TemporalType.TIMESTAMP)
    private Date accessTs;
}

Now I wanted to run a JPQL query to get the list of the users with their last access date. Unfortunately the following query doesn't return the users that never accessed the system, i.e exist in UserClient table, but don't have any record in UserAccess one.

SELECT ua.user, MAX(ua.accessTs) FROM UserAccess ua RIGHT JOIN ua.user

Do I miss something? Is that correct use of RIGHT JOIN?

I'm using the latest Hibernate JPA release (4.0.0.CR1)

Answer 1

You should make the UserClient table the owner side of the relationship (which make more logical sense IMO). Then you can use a LEFT JOIN instead of a RIGHT JOIN.

SELECT uc, MAX(ua.accessTs) FROM UserClient uc LEFT JOIN uc.userAccess ua

Here's why left join on UserAccess works: