Q:

Getting Hibernate entities from inner join on the same entity

I am trying to get the last EmergencyJob carried out by each Employee (i.e. the one with the max endTime).
I have the following entities
EmergencyJob
@Entity
@Table(name = "emergencyjobs", schema = "simulator")
public class EmergencyJob {

    @Id
    private String jobId;
    private LocalDate dateLogged;
    private LocalTime timeLogged;
    private double easting;
    private double northing;
    private String priority;

    private Point location;

    @ManyToOne
    private Engineer assignedEngineer;
    private LocalTime startTime;
    private LocalTime endTime;

    ...
}

Engineer
@Entity
@Table(name = "engineer", schema = "simulator")
public class Engineer {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int engineerId;
    private Point homeLocation;

    ...
}

With the tables are generated by Hibernate I can do what I need in the database using the following query:
SELECT e.*
FROM simulator.emergencyjobs AS e
INNER JOIN (

    SELECT 
    assigned_engineer_engineer_id,
    max(end_time) as end_time
    FROM simulator.emergencyjobs
    GROUP BY assigned_engineer_engineer_id
) AS ee
ON e.assigned_engineer_engineer_id = ee.assigned_engineer_engineer_id
AND e.end_time = ee.end_time

But I can't figure out how to use this to retrieve a list of EmergencyJob entities in Hibernate.
Any help would be appreciated!

A:

This should do the trick (fetch is optional):
select ej 
from emergencyJob ej
  inner join fetch ej.assignedEngineer e
where ej.endTime = (select max(distinct sej.endTime) 
                  from emergencyJob sej 
                  where sej.assignedEngineer = e)

Also remember to define the join column on EmergencyJob entity:
@ManyToOne
@JoinColumn(name = "assigned_engineer_engineer_id")
private Engineer assignedEngineer;   

