Multiple Column In clause Parameter

[LukeChow]

@Entity
@Data
class Image {
            private long id;
            private long imgId;
            private int index;
}
//In service, select all that its index and imgId in a map
HashMap<Integer, Long> usedImageMap = ...
List<Tuple> list = new ArrayList<>();
//But how to initial list from usedImageMap???
queryFactory.selectFrom(qImage)
                .where(Expressions.list(qImage.index, qImage.imgId).in(list))
                ....

Maybe this is a solution, but it is ugly!

usedImageMap.forEach((index, imgId) -> {
            list.add(new Tuple() {
                @Nullable
                @Override
                public <T> T get(int i, Class<T> type) {
                    if (i == 0) return (T)index;
                    return (T)imgId;
                }

                @Nullable
                @Override
                public <T> T get(Expression<T> expr) {
                    if (expr.equals(qImage.index)) return (T)index;
                    return (T)imgId;
                }

                @Override
                public int size() {
                    return 2;
                }

                @Override
                public Object[] toArray() {
                    return Arrays.asList(index, imgId).toArray();
                }
            });
        });

[jwgmeligmeyling]

Well what happens if qImage.index is not unique? Allow Multimap as input variable as well as a Map? Then what if the input index is actually inversed? How to handle tuples greater than 2, like Expressions.list(a, b, c).in(...)? IMO these decisions will always be use case specific so its not particularly sensible to build something in QueryDSL for this (I am open to suggestions though).

Meanwhile: what about:

Expressions.list(qImage.index, qImage.imgId).in(usedImageMap.entrySet().stream()
                .map(entry -> Expressions.list(Expressions.constant(entry.getKey()), Expressions.constant(entry.getValue())))
                .toArray(Expression[]::new))

[oliveiragabriel07]

I'm trying to do the same thing here, but the generated query contains the following:

# ...
where
        (
            splitbenef4_.PRE_CONTRACT_PAYMENT_HOLDER_DOCUMENT , splitbenef4_.PRE_CONTRACT_PAYMENT_OWNER_DOCUMENT , transactio0_.DUE_DATE , paymentbra3_.DESCRIPTION
        ) in (
            ? , ? , ? , ? , (
                ? , ? , ? , ?
            ) , (
                ? , ? , ? , ?
            )
        )

It seems that it doesn't work for the first element in array. Here is the full code:

    final QTransactionPayment payment = QTransactionPayment.transactionPayment;
    final QSplitBeneficiary splitBeneficiary = QSplitBeneficiary.splitBeneficiary;
    final QTransaction transaction = QTransaction.transaction;
    final QPaymentBrand paymentBrand = QPaymentBrand.paymentBrand;

    final Expression[] tuples = urs.stream().map(ur ->
    Expressions.list(Expressions.constant(ur.getDocumentoUsuarioFinalRecebedor()),
        Expressions.constant(ur.getDocumentoTitular()),
        Expressions.constant(ur.getDataLiquidacao()),
        Expressions.constant(ur.getCodigoArranjoPagamento())))
        .toArray(Expression[]::new);

    final JPQLQuery<BeneficiaryPaymentDto> query = new JPAQuery<>(entityManager);
    query
        .select(Projections.constructor(BeneficiaryPaymentDto.class,
            payment.id, payment.transaction.id, splitBeneficiary.preContractPaymentHolderDocument,
            splitBeneficiary.preContractPaymentOwnerDocument, payment.dueDate,
            paymentBrand.description))
        .from(payment).innerJoin(payment.transaction, transaction)
        .innerJoin(transaction.paymentBrand, paymentBrand)
        .innerJoin(splitBeneficiary).on(payment.receiver.id.eq(splitBeneficiary.id))
        .where(Expressions.list(splitBeneficiary.preContractPaymentHolderDocument,
            splitBeneficiary.preContractPaymentOwnerDocument, payment.dueDate,
            paymentBrand.description).in(tuples));

    return query.fetch();

And then it fails with the following message:

Caused by: java.sql.SQLException: Operand should contain 4 column(s)

Updated to querydsl 5.0.0 and Hibernate 5.3.27.Final and still have the same problem. Can you please provide any guidance? Perhaps its the same issue pointed at: #3200

[kashev]

I was having a similar issue and found this answer by @jwgmeligmeyling from a few days later -
#2926

It suggests that using nested Expression.list is not appropriate, and instead we should be using Template expressions.

[LukeChow]

Well what happens if qImage.index is not unique? Allow Multimap as input variable as well as a Map? Then what if the input index is actually inversed? How to handle tuples greater than 2, like Expressions.list(a, b, c).in(...)? IMO these decisions will always be use case specific so its not particularly sensible to build something in QueryDSL for this (I am open to suggestions though).

Meanwhile: what about:

Expressions.list(qImage.index, qImage.imgId).in(usedImageMap.entrySet().stream()
                .map(entry -> Expressions.list(Expressions.constant(entry.getKey()), Expressions.constant(entry.getValue())))
                .toArray(Expression[]::new))

Thank you for your reply.