CODEWITHSUNDEEP
spring-data-jpaself-join
VVK
VVK

Reputation: 43

Spring Data JPA self join query giving unexpected result

I have a mysql database table 'Faculties' like below,

enter image description here

My entity class (FacultyEntity) is as below

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "Faculty_Id")
private Integer facultyId;

@Column(name = "Faculty_Name", length = 100, nullable = false, unique = true)
private String facultyName;

@ManyToOne(cascade = { CascadeType.REFRESH })
@JoinColumn(name = "HOD_ID", nullable = true)
private FacultyEntity hodId;

I want to display the result in my application sorted by HOD's and and then faculties. This is what I've tried

@Query("from FacultyEntity f ORDER BY f.hodId.facultyName, f.facultyName")

But I'm getting total 4 rows sorted like this. I think it is because of the null value in the HOD_ID column, But I'm not sure. I'm newbie here.

enter image description here

The expected result will be

enter image description here

Please help me to fix this issue.

Upvotes: 0

Views: 1708

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24423

When you do f.hodId in the query, it implicitly translates to inner join, which eliminates the two rows with null in HOD_Id column.

Try this instead

@Query("from FacultyEntity f left join f.hodId hod ORDER BY hod.facultyName, f.facultyName")

Upvotes: 1

Related Questions