How to handle optional SEEK clause?

[Brendan Long]

Hi,

I'm trying to write Jooq queries in the way the manual suggests, without a bunch of var usage, but I can't figure out how to write an optional seek.

What I want to do is default to not seeking (for the first page) and then when querying my second page do a seek. Right now my code is written like this:

Optional<Cursor> after = ...;
var baseQuery = ctx.selectFrom(EXAMPLE).orderBy(EXAMPLE.CREATED_AT, EXAMPLE.ID);

return after
     .map(a -> baseQuery.seekAfter(a.createdAt.atOffset(ZoneOffset.UTC), a.id))
     .orElse((SelectSeekLimitStep<ExampleRecord>) baseQuery)
     .limit(limit)
     .fetchStreamInto(EXAMPLE)
     .toList();

I tried using .seek(null, null) as the default, but that just causes it to return 0 results (since there's no row with created_at > null).

Is this a case where I need to write the query dynamically like this or is there some workaround that I'm missing?

Thanks,

Brendan

[Lukas Eder]

The best way to pass optional clause arguments starting from jOOQ 3.17 is to pass DSL::noField. Unfortunately, this doesn't seem to work yet for SEEK, which I'll fix right away:

https://github.com/jOOQ/jOOQ/issues/14395

After that fix, you'll write:

ctx.selectFrom(EXAMPLE)

   .orderBy(EXAMPLE.CREATED_AT, EXAMPLE.ID)

   .seek(

       condition ? noField(EXAMPLE.CREATED_AT) : c,

       condition ? noField(EXAMPLE.ID) : i)

   .limit(limit)

   .fetch();

A workaround is what you did, or you can always access the underlying mutable SelectQuery object via SelectFinalStep::getQuery, which allows for constructing the query a bit more imperatively (or you start out this way via DSLContext::selectQuery).

I'd keep your Optional approach for now, and migrate to using noField() after the above fix.

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9aa3d6ba-895e-431c-8263-e43f21bc2e3cn%40googlegroups.com.

[Brendan Long]

Thanks! I’ll keep my current approach and then update once 3.18 is out.

Brendan

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/0D7Tmuk79U8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4O%3DwUf3EsqV%2BJ-BKgig1aPo%2B_O-zrp3c4ZihpJ7RjHTQ%40mail.gmail.com.