Trying to join based on the ID of A that has at least one of the IDSs of table B. A.ID is of type varchar, B.IDS(which is converted as set) is of type varchar[]. I tried like below, but I get an error.

create.select()
    .from(A)
    .join(B)
    .on(A.ID.equal(any(B.IDS))) // Cannot resolve method 'equal(QuantifiedSelect<Record1<T>>)'
    .where(other conditions)

Correct jooq code matching below query.

select A.ID, B.ID from A a, B b where a.id = ANY(b.ids) and (other conditions)

CodePudding user response：

The problem seems to be in this part of your question:

B.IDS(which is converted as set) is of type varchar[]

I'm assuming, you used a data type converter to turn your Field<T[]> into a Field<Set<T>>? While that is useful for projecting "better" types than the out of the box types jOOQ supports, it will prevent you from using some data type specific API, in this case array specific API, including the DSL.any(Field<T[]>) operator.

You have at least three options to work around this in this specific case:

• Coerce the type back to String[] using DSL.coerce()
• Use plain SQL templating, which is always a useful workaround when running into some limitation
• Use rawtype casts to remove type safety

CodePudding user response：

Would this do the trick?

(I assume the two table are SQL tables)

SELECT a.ID
FROM A AS a 
WHERE a.ID IN (SELECT ID FROM B)