QueryDsl - subquery in collection expression

Question

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.

score 32 · Accepted Answer · answered Feb 17 '14 at 17:52

32

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.

answered Feb 17 '14 at 17:52

Timo Westkämper

21,824
5
78
111

22

Seems like `JPASubQuery` is no longer available in QueryDSL 4. Converted this answer to: `BooleanExpression exp = invoice.supplier.number.in(JPAExpressions.selectFrom(company) .where(company.active.isTrue()) .select(company.nu‌mber));` – Oliver Hernandez Mar 13 '17 at 16:14
@OliverHernandez How to achieve the same for Many to Mant relation? https://stackoverflow.com/questions/44830387/how-to-create-predicate-booleanexpression-for-many-to-many-relations-in-jpa – Vivek Vardhan Jun 30 '17 at 05:24

Jaiwo99 · Answer 2 · 2014-02-17T13:27:38.947

1

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);

edited Feb 17 '14 at 13:27

answered Feb 17 '14 at 10:47

Jaiwo99

9,687
3
36
53

The thing is I'm using a combination of spring-data-jpa and querydsl. I need to construct a querydls Predicate (which can be further combined with other predicates to narrow the search results). I need to fetch all active companies numbers and use it as IN() clause in first query. I was trying th like that, but it no success: `BooleanExpression exp = invoice.supplier.number.in( company.active.eq(true) )` – wiecia Feb 17 '14 at 13:17
@wiecia if you are using `in()`, make sure you have a collection as parameter, apparently `company.active.eq(true)` doesn't indicate a collection – Jaiwo99 Feb 17 '14 at 13:25
@wiecia i will update the query above to match your need – Jaiwo99 Feb 17 '14 at 13:26
yes this was just copy paste mistake, thanks, is there a way to express this in predicate/expression way? I mean in my class that creates this predicates I don't have access to HB session/EM (and dont'w want to have) it should be some generic subqery. – wiecia Feb 17 '14 at 14:19
my scenario is that i'm creating a list of predicates: `List predicates = Lists.newArrayList();` and at the end I'm putting it together to construct the final search query: `return BooleanExpression.allOf(predicates.toArray(new BooleanExpression[predicates.size()]));` this way I'm able to gather all user input and construct final query so i'm searching a way to construct that IN() subqery without need to inject HB session or jpa EM – wiecia Feb 17 '14 at 14:20
@wiecia i'm not sure I have understood what you mean, you can also build your query without subQuery. just use `BooleanExpression.allOf(invoice.supplier.number.eq(company.number).and( company.active.eq(true))));` if you have more question, pls specify it – Jaiwo99 Feb 17 '14 at 14:28
Seems like it's working with this construction: `BooleanExpression exp = invoice.supplier.number.in(new HibernateSubQuery().from(company).where(company.active.isTrue()).list(company.number));` but here I need to use Hibernate specific constructions. Just wonder if there's sth more generic – wiecia Feb 17 '14 at 15:00
@Jaiwo99 I need to create a Predicate for Many to Many relations. https://stackoverflow.com/questions/44830387/how-to-create-predicate-booleanexpression-for-many-to-many-relations-in-jpa Can you please have a look here? – Vivek Vardhan Jun 30 '17 at 05:26

QueryDsl - subquery in collection expression

2 Answers2

Linked