how to generate mulitple conditions in one exist subclause instead of one exist clause for each cond

[zhon...@gmail.com]

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

[timowest]

You will need to write the subquery explicitly, any() will create independent subqueries.

Here are some examples http://www.querydsl.com/static/querydsl/4.0.4/reference/html_single/#d0e407

[zhon...@gmail.com]

Is there a way to do this via predicates?

We are using spring data/jpa + querydsl, and the API only allow us to pass predicates to the findAll() method.  An example would be great.

Thanks!

[zhon...@gmail.com]

Found similar requirements back in 2014:
https://github.com/querydsl/querydsl/issues/1035

Just wonder if there is any followup/update on this?  As mentioned, using a separate subquery definition is not quite feasible for us because of spring data/JPA.

Thanks.

[timowest]

A subquery can be turned into a predicate via new JPASubQuery().from(...).where(...).exists().

That's the way to go for now.

[Chandu Reddy]

Hi,

is there any other approach other than building subquery manually.

Thanks
Chandra