Left join with condition

Hibernate ORM
1

I have a table “exam” and a table “assignee” , with many assignees for an exam with different roles.

I am trying to create a query to fetch all exams with the role for the current user, which could be not assigned to the exam at all. In SQL i would use a left join

SELECT exam.id, exam.title, assignee.role FROM exam JOIN assignee ON exam.id = assignee.exam_id AND user_id = :userId

I am not sure how to do this using hibernate.

My attempt so far:

I have a domain model for Exam like this:

@Table(name = "exam")
@Entity(name = "exam")
public class ExamDto {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @OneToMany(mappedBy = "exam")
    private List<AssigneeDto> assignees;

...
}

My thinking was that the list assignees has one record with the assignee for the current user, or is empty if the user is not assigned to the exam.

My query (which is not working):

public interface ExamRepository extends JpaRepository<ExamDto, String> {
    @Query("from exam as e left outer join fetch e.assignees as assignee with assignee.username = :username where e.id = 1 ")
    ExamDto getByUsername(@Param("username") String username);
}

Which gives the error " with-clause not allowed on fetched associations; use filters" . But i have tried many different variations.

My question is what would be the best way to approach this problem? Do i need to change my domain object or the query? Thanks for any help, it is much appreciated.

2

First of all, I wouldn’t call the entities DTOs, as they usually aren’t that. A DTO is usually a level above that and in fact is what you need here. You will have to change the query to something that is similar to the SQL query you already had in mind:

public interface ExamRepository extends JpaRepository<ExamDto, String> {
    @Query("select e.id, assignee from exam e left join e.assignees assignee on assignee.username = :username where e.id = 1 ")
    List<Object[]> getByUsername(@Param("username") String username);
}

or if the exam is really fixed:

public interface ExamRepository extends JpaRepository<ExamDto, String> {
    @Query("select assignee from exam e left join e.assignees assignee on assignee.username = :username where e.id = 1 ")
    List<AssigneeDto> getByUsername(@Param("username") String username);
}

The error you were getting is due to filtering of fetched collections not being possible, as that might cause issues when re-synchronizing with the database, essentially leading to deleting non-fetched elements. What you want to do though can be easily done with Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(ExamEntity.class)
public interface ExamDto {
    @IdMapping
    Long getId();
    String getTitle();
    @Mapping("assignees[username = :username]")
    Set<AssigneeDto> getAssignees();

    @EntityView(AssigneeEntity.class)
    interface AssigneeDto {
        String getRole();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

ExamDto a = entityViewManager.find(entityManager, ExamDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: Blaze Persistence - Entity View Module

Page<ExamDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary! So in the end, this would generate the same SQL query that you already had in mind.

3

Thanks for your elaborate answer. Sorry for my late reply, I was on leave for a week.

If I understand it correctly, your first example returns a tuple a list, with exam id and a assignee object. So the join condition exam.id = assignee.exam_id will automatically be applied?

I will look into Blaze-persistence to be able to grasp your second example. I have some trouble undertanding:

 @Mapping("assignees[username = :username]")
    Set<AssigneeDto> getAssignees();

I would think the join condition should be on exam id, or it should return a signle AssigneeDto.

4

Sure, no problem. I was on PTO as well last week, so sorry for taking so long :slight_smile:
The join condition based on exam id is already defined for the association as you annotated it. So joining or de-referencing the association will automatically use that join condition on the SQL level. The condition in the brackets is just an additional condition that is added to the join predicate.