QueryDSL for math expressions?

[Amit Kumar]

Respected Members,

I need to convert below query into QueryDSL. 

```

select pdo.* from premium_order pdo inner join order o on pdo.order_id = o.orderid where

pdo.user_status = 'SUSPENDED' and (pdo.end_time + extract(epoch from interval '1 day' * o.deletiongrace)::bigint)<extract(epoch from now())::bigint ;

```

I am struggling to convert below part. end_time is of type bigint i.e seconds and deletiongrace is integer type and stores number of days so first need to convert them to seconds and then finally compare their sum to current time in seconds i.e epoch time:

```

(pdo.end_time + extract(epoch from interval '1 day' * o.deletiongrace)::bigint)<extract(epoch from now())::bigint 

```

Here this extract, epoch, interval not sure how to fit?

 I have written below methods:

```

 public JpaQueryProvider getSuspendedOrdersOfExpiredQueryProvider(final Instant executionTimestamp)

    {

     QOrders qOrders = QOrders.Orders;

        QPremiumOrder qPremiumOrder = QPremiumOrder.premiumOrder;

        JPAQuery<PremiumOrder> jpaQuery = getExpiringOrdersQuery()

                .innerJoin(qOrders)

                .on(qPremiumOrder.orderId.eq(qOrders.orderId))

                .where(setUserStatusAndExpiryTime(executionTimestamp, qOrders));

        return new QueryDSLQueryProvider<>(jpaQuery);

}

```

How to write/correct below method for epoch expression?

```

private BooleanExpression setUserStatusAndExpiryTime(final Instant executionTimestamp, QOrders qOrders)

    {

        return (qPremiumOrder.userStatus.eq(SUSPENDED))

                .and((qPremiumOrder.endTime + qOrders.deletionGrace).loe(executionTimestamp.getEpochSecond()));

///needs CORRECTION HERE

    }

```

Regards,

Amit

[Nathan Molinari]

Hello, 

take a look in class SqlExpressions, it has some methods that operates with dates like dateadd and anothers.

I hope it helps. 

[Richard Richter]

Hi

I see a couple of "extract" usages in PostgreSQLTemplates and SQLTemplates, but none of them "epoch" related.

You can create your own templates though - you can even put the whole complicated expression into a single template. The part after AND is obviously a boolean, so you can try something like:

import com.querydsl.core.types.dsl.Expressions;

...

....and(Expressions.booleanTemplate(

" ({0} + extract(epoch from interval '1 day' * {1})::bigint)<extract(epoch from now())::bigint",

pdo.end_time, // path expression for pdo.end_time

o.deletiongrace

))

Bit extreme, but very flexible (until you want to migrate the same code to other databases).

There is nothing stopping you from inserting anything in the generated query really.

Richard