QueryDsl Tuple in List

Question

Assume me having a table of cats with plenty of columns. I am trying to do equivalent of this query in QueryDsl:

select * from cat where (cat.pattern, cat.breed) in (('calico', 'Siberian'), ('grey', 'Siamese'), ('tabby', 'Maine Coon'));

In Java I have this class for parametrizing my cats:

class CatParameters {
    public String pattern;
    public String breed;
}

And this method (unfortunately incorrect) to fetch cats from database:

public List<CatDto> getCatsByParameters(List<CatParameters> params) {
// something like this
QCat cat = QCat.cat;
return query.from(cat)
    .where(Expressions.list(cat.pattern, cat.breed).in(params))
    .list(ConstructorExpression.create(CatDto.class, cat.field1, cat.field2, cat.field3, .../* etc */))
}

This obviously leads to an error "Cannot resolve method 'in(java.util.List<my.package.name.Cat>)'".

So how can I query some cats with (colX, colY) being in list of java objects with those properties?

UPD: I have found this question on fairly similar topic with subquery instead of collection, yet I wonder if there is a way to do it with collection (or maybe somehow create List<Tuple> from List<CatProperties>).

Depending on the underlying query language this may be supported. Assuming you're using JPQL / HQL under JPA, this will unfortunately not work, as its not supported by the query language. — Jan-Willem Gmelig Meyling, Oct 04 '21 at 11:55
@Jan-Willem Yes, JPQL under JPA. I have come up with a workaround that iterates over the collection and constructs JPQL expression as string by joining `String.format("(%s, %s)", pattern, breed)` and then uses it in Expressions.booleanTemplate(...). Thought there was a somewhat neater and tidier way to do this. — Olegiwe, Oct 06 '21 at 11:50
@Olegiwe how did you use Expression.booleanTemplate here? I've create the joined String but the following gives me the wrong query: .where(Expressions.booleanTemplate("({0}) in ({1})", Expressions.list(cat.pattern, cat.breed), joinedString)) — Gabriel Oliveira, Jan 09 '23 at 12:57

score 0 · Answer 1 · answered Feb 16 '23 at 19:09

Per the maintainers of QueryDSL, using either CollectionExpression or nested Expression.list isn't the right approach, and instead one needs to use Template expressions (as hinted at in the comments on this question). Here's how this might look:

public List<CatDto> getCatsByParameters(List<CatParameters> params) {
    QCat cat = QCat.cat;
    return query.from(cat)
            .where(Expressions.list(cat.pattern, cat.breed).in(Expressions.list(
                    params.stream()
                            .map(catParam -> makeTuple(
                                Expressions.constant(catParam.pattern), Expressions.constant(catParam.breed)))
                            .toArray(new Expression[0])
            )))
            .list(ConstructorExpression.create(CatDto.class, cat.field1, cat.field2, cat.field3, .../* etc */))
}

private static SimpleExpression<Object> makeTuple(Expression<?>... args) {
    return Expressions.template(Object.class, "({0}, {1})", (Object[]) args);
}

QueryDsl Tuple in List

1 Answers1