Hi Rob, Thanks a lot for your comment.
Yes indeed, better examples would be very helpful. This issue, for example, is overdue (recursive CTE example): https://github.com/jOOQ/jOOQ/issues/6562 It will be a very good opportunity to put a disclaimer in the manual. jOOQ supports CTE, and does so very well in case your query is dynamic or vendor agnostic (I'm just now implementing a fix that can push up nested CTE to the top level of a SELECT: https://github.com/jOOQ/jOOQ/issues/3175, and even of any DML statement: https://github.com/jOOQ/jOOQ/issues/11586, in case a dialect doesn't support nesting, e.g. SQL Server). But that doesn't mean that jOOQ has to be used for *everything*. When I look at your examples, I wonder if a mix between using jOOQ for the dynamic/fast-changing parts and a SQL view or table valued function (TVF) for the static parts could have been an option. Both views and TVFs are supported by jOOQ's code generator and are heavily underappreciated. Given that I don't think jOOQ will be able to add enough type safety to CTE (or derived table) building (there's https://github.com/jOOQ/jOOQ/issues/1969, but it will just add some type safety, not a great user experience, I think), I think moving some logic into views or TVFs is often an option worth considering. Regarding this particular message: from(extras).join(MARKERSET_MEMBER).on(extras.field("xord") == > (MARKERSET_MEMBER.ORDINAL)) > java equals operator silently kills it Is this Scala or Groovy? I've considered adding a @Deprecated annotation on Field.equals(). There's already Javadoc warning of this API misuse, but deprecation warnings might be even better. Would that have helped in your case? On Mon, Mar 8, 2021 at 1:57 PM Rob Sargent <robjsarg...@gmail.com> wrote: > Other than the request for complex examples, this has been largely noise > on my part and I apologize. I get stuck, then frustrated, then reach out, > then finally get to a solution. > > But the issue of context for complex examples is very real. > > And I didn't use a CTE in the end! > > Thanks for understanding, > rjs > > On 3/8/21 3:27 AM, Lukas Eder wrote: > > Hi Rob, > > Like twitter, we sometimes wish to have an edit button on mailing lists, > right? :) > > I'm having difficulties to generate the "delta" of your different > messages. Some issues you ran into seem not strictly related, and others > have been superseded by a subsequent email. > > Was there a bug somewhere? Or a UX problem? Feel free to create an issue: > https://github.com/jOOQ/jOOQ/issues/new/choose > > Thanks, > Lukas > > On Fri, Mar 5, 2021 at 7:32 PM Rob Sargent <robjsarg...@gmail.com> wrote: > >> Perhaps a more addressable problem statement: referring to a previous CTE >> >> ( >> select distinct unnest(c.loci_ordinals) as ord >> from cliqueset c -- CTE referenced here >> where 12994 < any(c.loci_ordinals) or 12849 > any(c.loci_ordinals) >> ) >> >> In jOOQ I had to bring in the actual table containing the array >> >> Field<Integer> lowOrd = DSL.val(pterIndex); >> Field<Integer> highOrd = DSL.val(qterIndex); >> CommonTableExpression<Record1<Integer>> extras = >> name("xm").fields("xord") >> >> .as(selectDistinct(cliqueset.field("mord").cast(Integer.class).as("xord")) >> >> .from(cliqueset.join(LD_CLIQUE).on(cliqueset.field("qid").cast(UUID.class).eq(LD_CLIQUE.MARKERSET_ID) >> >> .and(cliqueset.field("qord").cast(Integer.class).eq(LD_CLIQUE.ORDINAL)))) >> .where(lowOrd.gt(DSL.any(LD_CLIQUE.LOCI_ORDINALS))) >> .or(highOrd.lt(DSL.any(LD_CLIQUE.LOCI_ORDINALS)))); >> >> On 3/5/21 10:06 AM, Rob Sargent wrote: >> >> I'm sure you've given up by now but the orderBy() needs to be on the >> union of course not the second CTE >> rjs >> >> On 3/5/21 8:25 AM, Rob Sargent wrote: >> >> Sorry, the error is >> >> .from(extras).join(MARKERSET_MEMBER).on(extras.field("xord") == >> (MARKERSET_MEMBER.ORDINAL)) >> >> java equals operator silently kills it >> >> On 3/5/21 7:58 AM, Rob Sargent wrote: >> >> Scrap that example. >> on(cliqueset.field("qid").cast(UUID.class).eq(LD_CLIQUE.MARKERSET_ID) >> become "on false" >> so I have more work to do. >> >> >> On 3/5/21 1:04 AM, Lukas Eder wrote: >> >> We don't have any, though we probably should (especially for CTE, there's >> a pending issue for that). What are you looking for, specifically? >> >> On Thu, Mar 4, 2021 at 10:40 PM Rob Sargent <robjsarg...@gmail.com> >> wrote: >> >>> Is there a repository of more complex examples of jOOQ code? I'm having >>> a lot of fun with CTEs and DSL.any and I think I'm conforming to the >>> website examples. >>> >>> -- >>> 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/0c174979-b542-eca3-44cb-adc47ade1310%40gmail.com >>> . >>> >> -- >> 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/CAB4ELO7FRKpTKcKXEGfaB533G0XmmARuSuFNkoVNgbANCP5aqg%40mail.gmail.com >> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO7FRKpTKcKXEGfaB533G0XmmARuSuFNkoVNgbANCP5aqg%40mail.gmail.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/7a23d1dd-af96-17da-955b-7f3adc9b9fa9%40gmail.com >> <https://groups.google.com/d/msgid/jooq-user/7a23d1dd-af96-17da-955b-7f3adc9b9fa9%40gmail.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/CAB4ELO6T-TSS4iUqGOPnPrPRs1-EzXbzBBGMpdW-tKiJB8MJyA%40mail.gmail.com > <https://groups.google.com/d/msgid/jooq-user/CAB4ELO6T-TSS4iUqGOPnPrPRs1-EzXbzBBGMpdW-tKiJB8MJyA%40mail.gmail.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/a43084be-df56-ec84-6100-9197f8d66c89%40gmail.com > <https://groups.google.com/d/msgid/jooq-user/a43084be-df56-ec84-6100-9197f8d66c89%40gmail.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/CAB4ELO5J%2BtH6zbdb--1GDwDOcm-MVsh5hHXeKt3m%3DG01c3aBBw%40mail.gmail.com.
