8

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)

Hash
  • 4,647
  • 5
  • 21
  • 39
Gennady Shumakher
  • 5,637
  • 12
  • 40
  • 45

1 Answers1

6

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:

SQL left join visual description

Matt Ball
  • 354,903
  • 100
  • 647
  • 710
  • You missed the ua alias on us.userAccess. Also, having the inverse OneToMany association is sufficient. No need to make it the owner side (which is impossile in JPA, BTW) – JB Nizet Aug 26 '11 at 20:53
  • @Matt User can be involved in many other objects, so to me it's more logical these objects to point to User then otherwise. Do you think anything wrong with my RIGHT JOIN? – Gennady Shumakher Aug 26 '11 at 21:05
  • @JB Nizet thanks for the correction, I edited this in a hurry. I haven't tried the above query myself. Are you sure that JPA can left join as you describe? Feel free to edit my answer for less fail, or just post your own based on mine... – Matt Ball Aug 26 '11 at 21:17