First we are using: Spring Data/JPA + QueryDSL (3.6.5).
The situation is we have two entity class (Report and DefinedReport) with 1 to many relationship:
//********* Report
public class Report implements Serializable {
@OneToMany(mappedBy = "report")
private Set<DefinedReport> definedReports = new HashSet<>();
...
}
//********* Defined Report
public class DefinedReport implements Serializable {
@ManyToOne @JoinColumn(name = "rpt_id")
private Report report;
@Column(name = "active_flg", length = 1)
private String activeFlg;
@Column(name = "is_default", length = 1)
private String isDefault;
...
}
Now we need to find all the reports whose defined reports meet 2 requirements: activeFlg=="Y" and isDefault="N".
Since we use Spring data + querydsl, we build the 2 predicates from the entity path and AND them together:
rEport.definedReports.any().isDefault.eq("N")
rEport.definedReports.any().activeFlg.eq("Y")
However, the end result is not exactly what we want:
select rEport from rEport rEport
where ( exists
(select 1 from rEport.definedReports as rEport_definedReports_21640
where lower(rEport_definedReports_21640.defRptNm) like ?3 escape '!') and
-- notice two exist subclauses here
exists (select 1 from rEport.definedReports as rEport_definedReports_c169d
where lower(rEport_definedReports_c169d.isDefault) = ?4) and
exists (select 1 from rEport.definedReports as rEport_definedReports_808e1
where lower(rEport_definedReports_808e1.activeFlg) = ?6)
)
What we expected:
select rEport from rEport rEport
where (
exists
(select 1 from rEport.definedReports as rEport_definedReports_c8f53
where lower(rEport_definedReports_c8f53.defRptNm) like ?3 escape '!' and
lower(rEport_definedReports_c8f53.isDefault) = ?4 and
lower(rEport_definedReports_c8f53.activeFlg) = ?6)
)
So in summary, how could we achieve the expected query above when the conditions/predicates are on the child entity in a 1 to many relationship?
Thanks,
Kevin