HQL for getting min

Question

I have following entities:

Company.class:

public class Company {
    @JoinTable(name = "company_employee", joinColumns = @JoinColumn(name = "company_id") , inverseJoinColumns = @JoinColumn(name = "employee_id") )
    @ManyToMany(fetch = FetchType.LAZY)
    private Set<Employee> employees;

@OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "company")
private List<Score> scores;

    @JoinTable(name = "company_factor", joinColumns = @JoinColumn(name = "company_id") , inverseJoinColumns = @JoinColumn(name = "factor_id") )
    @ManyToOne(fetch = FetchType.LAZY)
    private Factor factors;
}

and Employee.class

    public class Employee {
             @ManyToMany(fetch = FetchType.EAGER, mappedBy="employees")
             private Set<Company> companies;

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "company")
    private List<Score> scores;

             @JoinTable(name = "employee_factor", joinColumns = @JoinColumn(name = "employee_id") , inverseJoinColumns = @JoinColumn(name = "factor_id") )
             @ManyToMany(fetch = FetchType.LAZY)
             private Set<Factor> factors;
@Transient
    private int score;
    }

Factor.class doesn't contain any relationships.

Also, I have some entity Score that is unique for each combination Company-Employee. It looks like that: Score.class:

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "company_id", insertable = false, updatable = false)
    private Company company;

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name = "employee_id", insertable = false, updatable = false)
    private Employee employee;

    @Column(name = "score")
    private BigDecimal score;

If I get List, it will be a list of combinations Company and Employee instances, and sometimes Company or Employee can be repeated. The goal is to get List, filtered by Factor in Employee and showing only minimal score for each employee ordered in ascending order. Say, if exist combinations

employee1-company1, score=1
employee1-company2, score=2
employee2-company1, score=3
employee2-company4, score=5
employee3-company4, score6

ResultList should look like that:

employee1-company1, score=1
employee2-company1, score=3
employee3-company4, score=6

So employee should not repeat, but company could repeat in the List. I am not quite sure, how to do that. What I achieved is showing unique results in ascending order, but they don't show min score. I used HQL:

    select distinct e from Score e 
left outer join fetch e.company 
left outer join fetch e.company.factors 
left outer join fetch e.employee 
left outer join fetch e.employee.factors ef 
where ef.factor_id=:factor_id 
group by e.employee.employee_id 
order by e.score asc

Could anybody help how to achieve what I need? Thank you.

UPDATE1:

I decided to go another way. Now I am getting it via Employee using this query:

select distinct e from Employee e join e.scores es order by es.score asc

It seems it's exactly what I need, but how to put in the query the minimum es.score to the field score of Employee object? Maybe there is some way to substitute e.score by es.score?

Answer 1

As a solution I switched to entityManager.createNativeQuery("some native sql string"). I am satisfied with the result. Just for the case, question about SQL query is here The only drawback is that it's impossible to use join fetch, hence N+1 select problem is here, but I plan to fetch quite small chunk of data, so it's bearable.