CODEWITHSUNDEEP
javaspringspring-dataquerydsl
wiecia
wiecia

Reputation: 285

QueryDsl - subquery in collection expression

I'm using spring-data-jpa and querydsl (3.2.3)
I have a scenario where I'm creating set of predicates based on user filer/input. All of these comes to BooleanExpression.

My simplified model looks as following: 

@Entity
public class Invoice {
    @ManyToOne
    private Supplier supplier;
}

@Entity
public class Supplier {
    private String number;
}

@Entity
public class Company {
    private String number;
    private boolean active
}

Now, what I'm struggling with is this query: 

SELECT * FROM Invoice WHERE invoice.supplier.number in (SELECT number from Company where active=true)

So basically I need to subquery in CollectionExpression like format that will fetch all companies numbers and sets this into in() expression.

My spring-data repositories implements CustomQueryDslJpaRepository which in turn extends JpaRepository and QueryDslPredicateExecutor.
I hope the answer to this is straightforward, but I'm quite new to querydsl and didn't find the solutions so far.

Upvotes: 14

Views: 38721

Answers (2)

Timo Westkämper
Timo Westkämper

Reputation: 22200

Here is a variant of jaiwo99's answer in a more JPAesque form

BooleanExpression exp = invoice.supplier.number.in(new JPASubQuery()
    .from(company)
    .where(company.active.isTrue())
    .list(company.nu‌​mber));

Feel free to merge this into the original answer.

Upvotes: 32

Jaiwo99
Jaiwo99

Reputation: 10017

Try this: 

QInvoice invoice = QInvoice.invoice;
QCompany company = QCompany.company;

List<Invoice> list = new HibernateQuery(sessionFactory.getCurrentSession())
      .from(invoice).where(
      new HibernateSubQuery().from(invoice, company).where(
              invoice.supplier.number.eq(company.number).and(
              company.active.eq(true))).exists()).list(invoice);

Upvotes: 1

Related Questions