Multicolumn WHERE-IN clause in QueryDSL

Question

I'm new to QueryDSL and would to be able to assemble a query with multiple columns in the WHERE-IN clause, like the following query:

selec T1.COL1, T1.COL2, .... T1.COL10 
from T1
where (T1.COL1, T1.COL2) IN (select T2.COL1, T2.COL2 from T2 WHERE T2.COL3='a' AND T2.COL4='b')

I have the part of the main query down:

List<Tuple> result = queryFactory.select(t1.col,...,t1.col10)
.from(t1)
.where(???) // This is the part I'm missing
.fetch();

But I don't know how to implement the where clause. How can this be done in QueryDSL?

Thanks in advance!

Use joins, like described [here](http://stackoverflow.com/a/1136382/2078908). — ursa, Jun 15 '16 at 17:10
@ursa: a join is not necessarily a replacement for an IN condition — , Jun 24 '16 at 13:03

score 8 · Accepted Answer · answered Jun 10 '17 at 15:21

8

I had the same problem and it took me a day to find the solution to it. You can use Expressions.list() to specify more than one column for the in clause.

So here's what you should have in your where() clause:

Expressions.list(t1.col1, t1.col2).in(
    JPAExpressions.select(t2.col1, t2.col2)
                      .from(t2)
                      .where(...)
)

Hope it helps someone.

answered Jun 10 '17 at 15:21

Timi

774
9
16

I no longer work on the QueryDSL project, so unable to test this answer in a reasonable time investment. I'm accepting because I trust that this is the way to do it. Thank you, Timi! – Tamara Aviv Jun 12 '17 at 15:51

score -1 · Answer 2 · edited Jul 06 '16 at 15:43

-1

I'm not sure about selecting multiple fields in the sub-query, but for one field it works as follows, using JPASubQuery:

ListSubQuery<Integer> subQuery = new JPASubQuery()
    .from(t2)
    .where(t2.col3.eq('a')).and(t2.col4.eq('b'))
    .list(t2.col1);

And then use it in your query:

List<Tuple> result = queryFactory.select(t1.col,...,t1.col10)
    .from(t1)
    .where(t1.col1.in(subQuery))
    .fetch();

Maybe multiple fields work by using ListSubQuery<Tuple>.

edited Jul 06 '16 at 15:43

Tamara Aviv

885
1
11
28

answered Jun 24 '16 at 13:01

Simon

857
5
14

1

But the question is about using more then one column in the `IN` condition. – Jun 24 '16 at 13:04

Multicolumn WHERE-IN clause in QueryDSL

2 Answers2