How to join 4 tables in 1 DTO with Jpa Query

Question

So I have 4 tables

An Employer

 @Entity

 @EqualsAndHashCode(callSuper = false)
 @Table(name = "employers")

 @NoArgsConstructor
 @AllArgsConstructor
 @JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "jobPostings"})
 @PrimaryKeyJoinColumn(name="employer_id", referencedColumnName = "id")
 public class Employer extends User {
  
  @Column(name = "company_name")
  private String companyName;

  @Column(name = "website")
  private String website;

  @Column(name = "phone_number")
  private String phoneNumber;
  
  @OneToMany(mappedBy="employer")
  private List<JobPosting> jobPostings;
  }

A City

@Data
@Entity
@Table(name="cities")
@AllArgsConstructor
@NoArgsConstructor
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "jobPostings"})
public class City {

    @Id
    @Column(name="id")
    private int id;
    
    @Column(name="city_name")
    private String cityName;
    
    @OneToMany(mappedBy="city")
    private List<JobPosting> jobPostings;
}

A Job Position

@AllArgsConstructor
@Data
@Entity
@Table(name="job_positions")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "jobPostings"})
public class JobPosition {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="job_position_id")
    private int id;
    
    @Column(name="title")
    private String title;
    
    @OneToMany(mappedBy="jobPosition")
    private List<JobPosting> jobPostings;
    
    
}

And A JobPosting(like a job advertisement)

@Entity
@Table(name="job_postings")
@Data
@NoArgsConstructor
@AllArgsConstructor
//@JsonIgnoreProperties({"hibernateLazyInitializer", "handler", "city", "jobPosition","employer"})

public class JobPosting {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private int id;
    
    
    
    @Column(name="job_requirements")
    private String jobRequirements;
    
    @Column(name="salary_min")
    private int salaryMin;
    
    @Column(name="salary_max")
    private int salaryMax;
    
    @Column(name="application_deadline")
    private LocalDate applicationDeadline;
    
    @Column(name="number_of_openings")
    private int numberOfOpenings;
    
    @Column(name="stream_date")
    private LocalDate streamDate;
    
    
    @ManyToOne()
    @JoinColumn(name="city_id")
    private City city;
    
    
    @ManyToOne()
    @JoinColumn(name="job_position_id")
    private JobPosition jobPosition;
    
    @ManyToOne()
    @JoinColumn(name= "employer_id")
    private Employer employer;
        
}

I have implemented the necessary mapping for all of them and it is working fine. However, I want to Join them in a DTO like:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class JobPostingWithJobPositionCityEmployerDto {

    private int id;
    
    private String jobRequirements;

    private int salaryMin;

    private int salaryMax;

    private LocalDate applicationDeadline;

    private int numberOfOpenings;

    private LocalDate streamDate;

    private String cityName;

    private String title;

    private String companyName;
}

to get the fields I want in a more clean way, I am trying to use @Query annotation of springframework.jpa but I can not quite manage it since I just learned about this, the query I am using is:

@Query(value ="Select new kodlamaio.hrms.entities.dtos.JobPostingWithJobPositionCityEmployerDto"
            + "(j.id, j.jobRequirements, j.salaryMin, j.salaryMax, j.numberOfOpenings, j.streamDate, j.applicationDeadline, c.cityName, p.title, e.companyName)"
            + " From Employer e Inner Join e.jobPostings j, "
            + "From City c Inner Join c.jobPostings j, "
            + "From JobPosition p Inner Join p.jobPostings j", nativeQuery = true)
    List<JobPostingWithJobPositionCityEmployerDto> getJobPostings();

I dont even know if this is the correct way to do this, I keep getting syntax error, I looked up answers but couldnt quite grasp what they were saying, there were a lot of different scenarios. So if anybody could help me with this Query and recommend some sources to learn about different commands, I would appreciate it so much, Thanks in advance.

Answer 1

Mapping the result of a query with a DTO using the new keyword in a query only works for JPQL, it will not work for SQL (which is what you are using).

It also looks to me if you are trying to write a too complex query as everything can be achieved/reached through the JobPosting class, which will implicitly do the join when using JPQL.

So instead of your native SQL writing a JPQL should fix it.

Something like

@Query(value ="Select new kodlamaio.hrms.entities.dtos.JobPostingWithJobPositionCityEmployerDto"
            + "(jp.id, jp.jobRequirements, jp.salaryMin, jp.salaryMax, jp.numberOfOpenings, jp.streamDate, jp.applicationDeadline, jp.city.cityName, jp.jobPosition.title, jp. employer.companyName)"
            + " From JobPosting jp)

Which should do the trick. Your JPA provider should be smart enough to figure out what to join and retrieve.