But filtering by ROW_NUMBER() isn't keyset pagination, that's OFFSET pagination. jOOQ has native support for keyset pagination via the SEEK clause: https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/seek-clause/
On Tue, Jun 20, 2023 at 3:12 PM adithya kamath <kamath...@gmail.com> wrote: > Saw some videos by Hussein Nasser and some other answers on the same > <https://dba.stackexchange.com/a/298041>. That offset, limit might not be > the most optimal approach, so was thinking of using something like keyset > pagination. > > On Tuesday, 20 June 2023 at 17:55:45 UTC+5:30 lukas...@gmail.com wrote: > >> Yeah, SQL transformation is a power use feature that most users of the >> jOOQ Open Source Edition don't need. In the meantime there have been 2-3 >> out of the box transformations added to jOOQ, which rely on this framework, >> unlike most other emulations which are much simpler. >> >> But as you said, you can implement this emulation manually using a >> derived table. Or, in your particular case, why not just use ORDER BY .. >> OFFSET .. FETCH? Is your actual query much more complex? >> >> On Tue, Jun 20, 2023 at 1:49 PM adithya kamath <kama...@gmail.com> wrote: >> >>> Hey Lukas, >>> That's a bummer but I see where you're coming from. So then guess I can >>> just add rowNumber in the SELECT statement and use the good ol' where. >>> >>> On Tuesday, 20 June 2023 at 16:32:43 UTC+5:30 lukas...@gmail.com wrote: >>> >>>> Hi Adithya, >>>> >>>> Thanks for your message. The QUALIFY clause is available in all >>>> editions if the underlying dialect supports it natively. To emulate it, we >>>> use the QUALIFY to derived table transformation, a transformation >>>> that relies on commercial API, and is thus only available in commercial >>>> editions, see: >>>> >>>> https://www.jooq.org/doc/latest/manual/sql-building/queryparts/sql-transformation/transform-qualify/ >>>> >>>> It's probably worth referring to that transformation section from the >>>> QUALIFY section of the manual. I'll change that right away. >>>> >>>> I hope this helps, >>>> Lukas >>>> >>>> On Tue, Jun 20, 2023 at 12:53 PM adithya kamath <kama...@gmail.com> >>>> wrote: >>>> >>>>> >>>>> Just realised, it's a slightly more complicated first part before >>>>> qualify with joins >>>>> On Tuesday, 20 June 2023 at 16:22:40 UTC+5:30 adithya kamath wrote: >>>>> >>>>>> I'm trying to implement qualify using >>>>>> var result2 = ctx.select(PRODUCT_MASTER.PRODUCT_LINE, >>>>>> PRODUCT_MASTER.PRODUCT_NAME, PRODUCT_MASTER.PRODUCT_SCALE) >>>>>> .from(PRODUCT_MASTER) >>>>>> >>>>>> .qualify(rowNumber().over().orderBy(PRODUCT_MASTER.PRODUCT_LINE.desc()).between(start, >>>>>> end)) >>>>>> .fetchInto(ProductMaster.class) >>>>>> >>>>>> >>>>>> But I'm getting data access exception saying it is not available for >>>>>> OSS, but Saw some links like this >>>>>> <https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/qualify-clause/> >>>>>> which >>>>>> show it can be used with OSS. Some other examples like this >>>>>> <https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/window-functions/window-order/> >>>>>> and >>>>>> this >>>>>> <https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/window-functions/window-frame/> >>>>>> showing >>>>>> it is available for OSS. Possibly I'm missing something, or a simple part >>>>>> of the above that is not OSS? >>>>> >>>>> -- >>>>> 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/7628394e-e00c-4011-bbae-b7a145bbe34dn%40googlegroups.com >>>>> <https://groups.google.com/d/msgid/jooq-user/7628394e-e00c-4011-bbae-b7a145bbe34dn%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>> 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/e18c0dc5-5e9e-4fd1-98fd-994ffd452e3dn%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/e18c0dc5-5e9e-4fd1-98fd-994ffd452e3dn%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/42f4156e-b45f-4909-8e22-3c64581a14dfn%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/42f4156e-b45f-4909-8e22-3c64581a14dfn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO61u6Qh%3Dmvrh9bvQ62G%2BYJXAvo6GL81SaN%3DDMzAwDn84w%40mail.gmail.com.
