Can't left join a second table

Question

I am trying to left join a second table but it doesn't show up. It just gives me all users instead of all users along with the FinishedExams.

This is the method in my repository:

public interface IUserRepository extends CrudRepository<User, Integer> {
    @Query("SELECT u FROM User u LEFT JOIN FinishedExam f ON u.id = f.user")
    List<User> getAllWithExams();
}

In my FinishedExamController:

@Autowired
private IFinishedExamRepository finishedExamRepository;

@Autowired
private IUserRepository userRepository;

@GetMapping("/allUsersWithExams")
@Fetch(FetchMode.SELECT)
public Iterable<User> getAllUsersWithTheirExams()
{
    return userRepository.getAllWithExams();
}

My User model:

@Entity
@Table(name = "users")
public class User implements Serializable {
    @Id
    @GeneratedValue()
    @Column(name = "id")
    private int id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "role_id", nullable = false)
    public Role role;

    public String getStudentNumber() {
        return studentNumber;
    }

    public void setStudentNumber(String studentNumber) {
        this.studentNumber = studentNumber;
    }

    @Column(name = "student_number", nullable = true)
    private String studentNumber;

    @Column(name = "first_name", nullable = true)
    private String firstName;

    @Column(name = "last_name", nullable = true)
    private String lastName;

    @Column(name = "email", nullable = true, unique = true)
    private String email;

    @OneToOne(fetch = FetchType.EAGER, mappedBy = "user")
    private FinishedExam finishedExam;

    @JsonIgnore
    @Column(name = "password", nullable = true)
    private String password;


    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @Column(name = "updated_at")
    private LocalDateTime updatedAt;


    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
            name = "event_users",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "event_id", referencedColumnName = "id")
    )

    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }

    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = createdAt;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }


    public LocalDateTime getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(LocalDateTime createdAt) {
        this.createdAt = createdAt;
    }

    public LocalDateTime getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(LocalDateTime updatedAt) {
        this.updatedAt = updatedAt;
    }

    public String getRole() {
        return role.getName();
    }

    public void setRole(Role role) {
        this.role = role;
    }
}

Finished exams model:

@Entity
@Table(name = "finished_exams")
public class FinishedExam implements Serializable {
    @Id
    @GeneratedValue()
    @Column(name = "id")
    private int id;

    @OneToOne(fetch = FetchType.EAGER)
    @Fetch(FetchMode.SELECT)
    @JoinColumn(name = "user_id")
    private User user;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "exam_id")
    private Exam exam;

    @Column(name = "finishedExam", nullable = false)
    private String finishedExam;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = createdAt;
    }


    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }

    public int getId() {
        return id;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Exam getExam() {
        return exam;
    }

    public void setExam(Exam exam) {
        this.exam = exam;
    }

    public String getFinishedExam() {
        return finishedExam;
    }

    public void setFinishedExam(String finishedExam) {
        this.finishedExam = finishedExam;
    }

    public void setId(int id) {
        this.id = id;
    }

    public LocalDateTime getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(LocalDateTime createdAt) {
        this.createdAt = createdAt;
    }

    public LocalDateTime getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(LocalDateTime updatedAt) {
        this.updatedAt = updatedAt;
    }
}

I can put whatever I want in the ON clause but nothing changes.

Answer 1

I can put whatever I want in the ON clause but nothing changes.

Of course nothing changes. You are performing a left join across a 1:1 relationship, selecting (only) the left entity, and not placing any filter criteria on that entity. The join criteria (and indeed the join itself) don't matter: your query is equivalent to SELECT u FROM User u.

Did you perhaps mean to perform an inner join instead?

Answer 2

You need to provide a path from your entity to target entity to join:

Replace the query

@Query("SELECT u FROM User u LEFT JOIN FinishedExam f ON u.id = f.user")

with

@Query("SELECT u FROM User u LEFT JOIN u.finishedExam f ON u.id = f.user.id")