Solving JPA query finding the last entry in connected list

Question

Following class structure is given:

class Job 
{
    String description;
    Collection<JobHistory> history;
}

class JobHistory
{
    Date assignDate;
    User jobOwner;
}

class JobOwner 
{
    String name;
    String id;
}

This class-structure is accessible on the db via JPA. In the DAO-Layer I can write queries in JPA syntax.

The Problem: I want a list with Job and JobHistory entries for a given owner with given id and who is the last one in the Jobhistory of the job (ordered by assignDate). Sounds quite complicated, perhaps simpler: give me all jobs and JobHistory where specified owner is the actual owner of the job.

Update: for clarity I will slightly change the names of the classes.

class Job 
{
    String description;
    Collection<JobOwnerHistory> history;
}

class JobOwnerHistory
{
    Date assignDate;
    User jobOwner;
}

class JobOwner 
{
    String name;
    String id;
}

Every Job has a history of his owners sorted by assignDate. The actual owner got the job last assigned (i.e. MAX(assignDate)). I want find for every job the JobOwnerHistory entry with MAX(assignDate) for a specific user User.

Answer 1

Try:

SELECT j, j.history FROM Job j JOIN User u WHERE u.name = :name

If I were to do this in EclipseLink, I would change it slightly:

public List<Job> getAllJobsForUser(String username) {
  List<Job> jobs = entityManager
    .createQuery("SELECT j FROM Job j JOIN User u WHERE u.name = :name")
    .setParameter("name", username)
    .setHint(QueryHints.BATCH, "j.history")
    .queryForList();
}

The difference? In the first version, you're returning two objects, so you have to retrieve them from a List or Object arrays whereas in the second, the query hint just loads all the job histories from an (assumedly) lazyy one-to-many relationship.

I don't know if Hibernate has an equivalent to this. Toplink Essentials doesn't. But it's one of my favourite features of EclipseLink.

Oh and obviously you can (and probably should) use a named query instead of an adhoc query like I've done (since those can be verified during the build).

Answer 2

I found the following answer for the query:

SELECT j, h FROM Job j JOIN j.history h JOIN h.jobOwner u
WHERE u.name = :name AND 
    (SELECT MAX(h2.assignDate) FROM Job j2 JOIN j2.history h2
     WHERE h2 member of j.history) = h.assignDate

The most important part in the query is the subselect with MAX(h2.assignDate) because I want to get the job and the newest entry in the owner-history.