complex examples

[Rob Sargent]

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.

[Lukas Eder]

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?

--
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/0c174979-b542-eca3-44cb-adc47ade1310%40gmail.com.

[Rob Sargent]

Thanks, Lukas, I did manage to get a working solution.

You're of course welcome to stop here, but I'll include my work for your
consideration.  First off, context is so much more of an issue in more
complex examples: I cannot concisely explain the point of this code
beyond saying that the CTE part is looking for "straggler markers". 
That I'm using the postgres ANY may disqualify this example (but the
related FIELD work needed should definitely be publicized).

All the best,
rjs

TL/DR

We have a SEGMENT in hand.
   version4019=# \d v20.segment (hundreds of millions)
                        Table "v20.segment"
         Column      |  Type   | Collation | Nullable | Default
   ------------------+---------+-----------+----------+---------
    id               | uuid    |           | not null |
    chrom            | integer |           | not null |
    markerset_id     | uuid    |           | not null |
    probandset_id    | uuid    |           | not null |
    startbase        | integer |           | not null |
    endbase          | integer |           | not null |
    firstmarker      | integer |           | not null |
    lastmarker       | integer |           | not null |
    events_less      | bigint  |           | not null | 0
    events_equal     | bigint  |           | not null | 0
    events_greater   | bigint  |           | not null | 0
    threshold_events | integer |           |          |

We need the list of markers BETWEEN firstmarker and lastmarker.
   version4019=# \d base.markerset_member; (0.5 million)
                     Table "base.markerset_member"
       Column    |       Type       | Collation | Nullable | Default
   --------------+------------------+-----------+----------+---------
    markerset_id | uuid             |           | not null |
    member_id    | uuid             |           | not null |
    ordinal      | integer          |           | not null |
    theta        | double precision |           |          |

And markers may be reused in various sets.
   version4019=# \d base.markerset_member; (0.5 million)
                     Table "base.markerset_member"
       Column    |       Type       | Collation | Nullable | Default
   --------------+------------------+-----------+----------+---------
    markerset_id | uuid             |           | not null |
    member_id    | uuid             |           | not null |
    ordinal      | integer          |           | not null |
    theta        | double precision |           |          |

Markers are also clumped in small clusters or "cliques", with the array
"LOCI_ORDINALS" comprised of a list of markerset_member ordinals. (The
cliques themselves have their own ordinal.
   version4019=# \d base.ld_clique;(0.5 million)
                          Table "base.ld_clique"
       Column     |        Type        | Collation | Nullable | Default
   ---------------+--------------------+-----------+----------+---------
    markerset_id  | uuid               |           | not null |
    ordinal       | integer            |           | not null |
    loci_ordinals | integer[]          |           |          |
    potential     | double precision[] |           |          |

Working SQL.
   with cliqueset as(
   -- get all members and any cliques referencing markers between
   -- first/last marker
   select l.*, m.*
   from base.ld_clique l join base.markerset_member m
        on (m.ordinal =any(l.loci_ordinals) and l.markerset_id = m.markerset_id)
   where l.markerset_id = '75832ca7-2303-4792-a023-d4d8f9a07471'
         and m.ordinal between 12849 and 12994
   ), extras as
   -- but some cliques will contain markers not include in the
   -- first/last range
   (
   select distinct unnest(c.loci_ordinals) as ord
   from cliqueset c
   where 12994 < any(c.loci_ordinals) or 12849 > any(c.loci_ordinals)
   )
   select s.*, s.ordinal, m.*
   from extras x join base.markerset_member s on x.ord = s.ordinal
        join base.marker m on s.member_id = m.id
   where s.markerset_id = '75832ca7-2303-4792-a023-d4d8f9a07471'
          and not x.ord between (12849) and (12994)
   order by s.ordinal
   \p\g

Working jOOQ.  (Sorta.  I may need to shift all ordinals to be zero
based, but that's another story)
    // I have a SegmentRecord in hand but I need to pad each end a
    // little. (Don't ask)
    int expander = 3 * getExpansionStep() + 1;  //Three steps of 5, plus the boundning marker
    int pterIndex = expander > sRec.getFirstmarker() ? 0 : sRec.getFirstmarker() - expander;
    int qterIndex = sRec.getLastmarker() + expander; // We could lookup max ordinal but the /between/ will safely stop

   
    // QUESTION: Apparently it's necessary to spell out the selected columns, asterisk() didn't work?
    CommonTableExpression<Record8<UUID,Integer,Integer[],Double[],UUID,UUID,Integer,Double>> cliqueset =
      name("qm").fields("qid", "qord", "qloci", "qpot", "msid", "mid", "mord", "mtheta")
      .as(select(LD_CLIQUE.MARKERSET_ID.as("qid"),
         LD_CLIQUE.ORDINAL.as("qord"),
         LD_CLIQUE.LOCI_ORDINALS.as("qloci"),
         LD_CLIQUE.POTENTIAL.as("qpot"),
         MARKERSET_MEMBER.MARKERSET_ID.as("msid"),
         MARKERSET_MEMBER.MEMBER_ID.as("mid"),
         MARKERSET_MEMBER.ORDINAL.as("mord"),
         MARKERSET_MEMBER.THETA.as("mtheta"))
          .from(LD_CLIQUE.join(MARKERSET_MEMBER).on(MARKERSET_MEMBER.ORDINAL.equal(DSL.any(LD_CLIQUE.LOCI_ORDINALS))))
          .where((LD_CLIQUE.MARKERSET_ID.eq(sRec.getMarkersetId()))
         .and(MARKERSET_MEMBER.ORDINAL.between(pterIndex).and(qterIndex))));

    //NOTE: This "Field<>" trick should be near the ANY examples
    Field<Integer> lowOrd = DSL.val(pterIndex);   
    Field<Integer> highOrd = DSL.val(qterIndex);
    //This CTE is looking for the "ordinal" of markers mentioned in cliques
    //but which are out side the original the pter/qterIndex range
    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))));
   
    Result<?>markerdata = context
      .with(cliqueset)
      .with(extras)
      .select(MARKERSET_MEMBER.asterisk(), MARKER.asterisk())
      .from(extras).join(MARKERSET_MEMBER).on(extras.field("xord") == (MARKERSET_MEMBER.ORDINAL))
      .join(MARKER).on(MARKERSET_MEMBER.MEMBER_ID.eq(MARKER.ID))
      .where(MARKERSET_MEMBER.MARKERSET_ID.eq(sRec.getMarkersetId())
             .and(extras.field("xord").cast(Integer.class).between(pterIndex).and(qterIndex)))
      // NOTE: I do a union because when I had the below as a separate select I
      // had trouble putting the two Result<?> sets together.
      .union(select(MARKERSET_MEMBER.asterisk(), MARKER.asterisk())
        .from(MARKERSET_MEMBER
          .join(MARKER).on(MARKERSET_MEMBER.MEMBER_ID.equal(MARKER.ID)))
        .where(MARKERSET_MEMBER.MARKERSET_ID.equal(sRec.getMarkersetId())
          .and(MARKERSET_MEMBER.ORDINAL.between(pterIndex).and(qterIndex)))
        .orderBy(MARKERSET_MEMBER.ORDINAL))
      .fetch();

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7FRKpTKcKXEGfaB533G0XmmARuSuFNkoVNgbANCP5aqg%40mail.gmail.com.

[Rob Sargent]

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:

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7FRKpTKcKXEGfaB533G0XmmARuSuFNkoVNgbANCP5aqg%40mail.gmail.com.

[Rob Sargent]

Sorry, the error is

.from(extras).join(MARKERSET_MEMBER).on(extras.field("xord") == (MARKERSET_MEMBER.ORDINAL))

java equals operator silently kills it

[Rob Sargent]

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

[Rob Sargent]

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))));

[Lukas Eder]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/7a23d1dd-af96-17da-955b-7f3adc9b9fa9%40gmail.com.

[Rob Sargent]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6T-TSS4iUqGOPnPrPRs1-EzXbzBBGMpdW-tKiJB8MJyA%40mail.gmail.com.

[Lukas Eder]

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? 

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a43084be-df56-ec84-6100-9197f8d66c89%40gmail.com.

[Gmail]

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.

Indeed I had to cast nearly everything in my compiles-but-wrong attempt

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? 

Well in my case (java) the == was just habit and that of course became “FALSE”.

I must go back and look for old Field.equals() that might still be lying around but I think I used .equal() or now .eq().

Cheers,

rjs

[Lukas Eder]

I see. Well, the on(Boolean) method that accepts this result has been deprecated in jOOQ 3.8 (https://github.com/jOOQ/jOOQ/issues/4763), and will be removed in 3.15 (https://github.com/jOOQ/jOOQ/issues/11242), so while it is still possible to type Field.equals(Field), it will no longer be possible to pass that expression to any jOOQ API where Field.eq(Field) or Field.equal(Field) was intended.