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();
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 <[email protected]
<mailto:[email protected]>> 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 [email protected]
<mailto:jooq-user%[email protected]>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/0c174979-b542-eca3-44cb-adc47ade1310%40gmail.com
<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 [email protected]
<mailto:[email protected]>.
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/2c6cb54c-956f-3a40-d3f3-c04a2486b469%40gmail.com.
