where IN clause - multi column (querydsl)

Question

I have three integer values of two pairs.
I would like to use this as a list of IN in the WHERE clause.

(2019, 5) (2019, 6) (2019, 7)

I want to use the above two-pair list in a query like this:

SELECT
    *
FROM
    WEEK_REPORT A
WHERE
    A.user_id IN ('test2','test5' ) AND
    (A.year, A.week_no) IN ((2019,4),(2019,5),(2019,6));

To that end, I wrote the source as follows:

// lastYear=2019, lastWeekNo=4
Tuple t = JPAExpressions.select(Expressions.constant(lastYear), Expressions.constant(lastWeekNo))
    .from(weekReport)
    .fetchOne();

// currentYear=2019, currentWeekNo=5        
Tuple t2 = JPAExpressions.select(Expressions.constant(currentYear), Expressions.constant(currentWeekNo))
    .from(weekReport)
    .fetchOne();

// nextYear=2019, nextWeekNo=4
Tuple t3 = JPAExpressions.select(Expressions.constant(nextYear), Expressions.constant(nextWeekNo))
    .from(weekReport)
    .fetchOne();

return queryFactory
    .select(weekReport)
    .from(weekReport)
    .where(weekReport.user.eq(user)
        .and(Expressions.list(weekReport.year, weekReport.weekNo).in(t, t2, t3)))
    .fetch();

However, the correct result is not output and an error occurs.

java.lang.UnsupportedOperationException: null
    at com.querydsl.jpa.JPASubQuery.fetchOne(JPASubQuery.java:66) ~[querydsl-jpa-4.1.4.jar:na]

I looked it up in the official document but it does not come out.
Is there a way?

Thank you.

Iolaiolande

You could rewrite the query as AND ((A.year = 2019 AND A.week_no = 4) OR (A.year = 2019 AND A.week_no = 5) OR ...) – Michal

What version of MySQL? "Row constructors" were poorly optimized before version 5.7.3. – Edgewise

Answer 1

how about to pass a list of tuples as one parameter

List<Tuple> params = ...

params = [(2019,4),(2019,5),(2019,6)]

return queryFactory
    .select(weekReport)
    .from(weekReport)
    .where(weekReport.user.eq(user)
        .and(Expressions.list(weekReport.year, weekReport.weekNo).in(params)))
    .fetch();

Bricker

Can you show the params example, because I got error, and I am using constant value – Freely

Answer 2

What you're looking for is sometimes called a row-variable or tuple variable. Your DBMS might not feature such a creature.

In general, IN is just shorthand for EXISTS, and you can skin that cat the long way:

SELECT * FROM WEEK_REPORT as A
WHERE
A.user_id IN ('test2','test5' )
and exists (
    select 1 from (
        select 2019 as 'year', 4 as 'week'
        union
        select 2019, 5
        union
        select 2019, 6
        ) as T
    where week = A.week and year = A.year
);

There are other ways. Basically, you want to get your row variables into a table of some kind, then test for existence in that table. If it were me, and the years and weeks change over time, I would create a static table to hold the 3 rows, so I could modify the search criteria without changing the query.

Damales

Answer 3

try this

SELECT
    *
FROM
    WEEK_REPORT A
WHERE
    A.user_id IN ('test2','test5' ) AND
    concat(A.year, A.week_no) IN (20194,20195,20196);

Pineal

In fact, I'm wondering how to express multiple columns in the IN clause as querydsl rather than native sql. – Iolaiolande

There are a hundred different packages that try to "help" users speak SQL. Most fail to have a way to do everything without some escape to "native SQL". (I cannot speak for querydsl.) – Edgewise

Answer 4

DECLARE @t TABLE (
  year int,
  week int
)
INSERT @t
  VALUES (2019, 4), (2019, 5), (2019, 6)

SELECT
  *
FROM WEEK_REPORT A
WHERE A.user_id IN ('test2', 'test5')
AND EXISTS (SELECT
  *
FROM @t T
WHERE A.year = T.year
AND T.week = A.week_no)

Tavern