Clever Geek Handbook

Using two fields with the "in" operator in QueryDSL

I need to write this query using QueryDSL:

select *
from table
where(field1, field2) in (
    select inner_field_1, inner_field2
    from ...
);

However, I don't know how to use two fields (field1 and field2) with the "in" operator in QueryDSL. I searched for it in the documentation, but I didn't see any example of the two fields.

This is what I have so far:

Expression<?>[] projection = {
    table.field1,
    table.field2
};

SQLSubQuery outterQuery= new SQLSubQuery()
    .from(table)
    .where([some expression].in(inneryQuery.list(projection))) // ???
    .groupBy(contentcache1.programId, contentcache1.id);

Any help would be appreciated

Thank you in advance

+3


source to share


3 answers


You can rewrite your original query as:

select *
from table, (select distinct inner_field_1, inner_field2 from ...) subquery
where field1 = subquery.field1 and field2 = subquery.field2


Then you don't need to use the operator IN

.

+1


source


You can express it through



SQLSubQuery outerQuery = new SQLSubQuery()
    .from(table)
    .where(Expressions.list(column1, column2, ...).in(inneryQuery.list(projection))) 
    .groupBy(contentcache1.programId, contentcache1.id);
+9


source


You can manually convert the string-value-expression IN

predicate to an equivalent predicate EXISTS

, which is likely to work with QueryDSL. Some details are explained in this blog post , which essentially explains how jOOQ automatically handles such SQL conversions for you, acting directly on the SQL AST, you should write:

DSL.using(configuration)
   .select()
   .from(TABLE)
   .where(row(TABLE.FIELD1, TABLE.FIELD2).in(
       select(INNER_FIELD1, INNER_FIELD_2)
       .from(...)
   ))

Your original query:

select *
from table
where(field1, field2) in (
    select inner_field_1, inner_field_2
    from ...
);

This is equivalent to this:

select *
from table
where exists (
    select 1
    from ...
    where table.field1 = inner_field_1 and table.field2 = inner_field2
)

... which I'm sure you can express using QueryDSL (unfortunately I don't know the API well enough to show the actual query).

Compatibility note

Chances are your database does not support this row type expression predicate anyway, in case you are safe with EXISTS

. At least these databases support this predicate:

  • DB2
  • HSQLDB
  • MySQL
  • Oracle
  • Postgres
+1


source






More articles:


All Articles