I need a WHERE
clause to check tuples IN
a list: (field1, field2) in (('1', 1), ('2', 2), ('3', 3))
. This is valid SQL in Postgres.
Dialect: POSTGRES
jOOQ Version: 3.9.6
What is the correct jOOQ syntax for this case?
jOOQ 3.9 documentation implies this is possible, but their example only gives degree 1: https://www.jooq.org/doc/3.9/manual/sql-building/conditional-expressions/in-predicate-degree-n/
This code gives an approximation of what I'm looking for, but I can't get the right type/data for referenceOrderIdLineNumbers
nor can I get the right SQL generated by jOOQ.
Collection<Row2<String, Integer>> referenceOrderIdLineNumbers = ...
List<Object[]> rows = dsl.select(... , field("count(TABLE3)", Integer.class )
.from(Tables.TABLE1)
.join(Tables.TABLE2).on(Tables.TABLE2.PK1.eq(Tables.TABLE1.PK1))
.join(Tables.TABLE3).on(Tables.TABLE3.PK2.eq(Tables.TABLE2.PK2))
.where(
row(Tables.TABLE1.FIELD1, Tables.TABLE2.FIELD2) // <-- what to
.in(referenceOrderIdLineNumbers) // <-- do here??
)
.groupBy(...)
.fetch();
This is working as intended for me. You might try having jOOQ log the SQL its generating for you and try running said SQL against your database directly.
References:
Collection<Row2<String, Integer>> field1Field2Collection = new LinkedList<>();
field1Field2Collection.add(row("1", 1));
field1Field2Collection.add(row("2", 2));
field1Field2Collection.add(row("3", 3));
Result<Record2<String, Integer>> field1Field2Results = dsl
.select(Tables.TABLE1.FIELD1, Tables.TABLE2.FIELD2)
.from(Tables.TABLE1)
.join(Tables.TABLE2).on(Tables.TABLE2.PK1.eq(Tables.TABLE1.PK1))
.where(row(Tables.TABLE1.FIELD1, Tables.TABLE2.FIELD2).in(field1Field2Collection))
.fetch();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With