Regarding the CTE, you don't have to cast. If you *know* you're referencing a field that corresponds to a field from a generated table, you can just write aka.field(ALIAS.PERSON_ID) (though you can't rename the field in the CTE, then)
For a better, purely SQL based solution: - If you're using PostgreSQL, you could use DISTINCT ON - If you're using Oracle, you could use FIRST (as in KEEP (DENSE_RANK FIRST ORDER BY ...)) Some other ideas here: https://blog.jooq.org/using-distinct-on-in-non-postgresql-databases Alternatively, you could still use fetchGroups() to collect the result in a Map<Key, List<Value>> Or, you use MULTISET_AGG to nest PersonRecord per AKA directly in SQL? Hope this helps, Lukas On Thu, May 26, 2022 at 7:52 AM Rob Sargent <[email protected]> wrote: > What are my options for the key in "fetchMap(key, record)"? > > I was getting duplicates (of what, we'll confess later) so I added the > "Distinct" seen below > > /* > * We believe all current person.name values are also in alias.aka > */ > private Map<String, PersonRecord> readKnowPersons(Map<String, > LinkageIndividual> pedIndivMap) { > Map<String, PersonRecord> fullMap = new HashMap<>(); > fullMap.putAll(ctx.selectDistinct() > .from(PERSON) > .join(ALIAS).on(PERSON.ID.equal(ALIAS.PERSON_ID)) > .where(ALIAS.AKA.in(pedIndivMap.keySet())) > .fetchMap(ALIAS.AKA, r -> r.into(PERSON))); > return fullMap; > } > > The duplicates are actually in the ALIAS.AKA so I went to > > private Map<String, PersonRecord> readKnowPersons(Map<String, > LinkageIndividual> pedIndivMap) { > //Apologies for the selectDistinct. If pedigrees overlap ALIAS may > have multiple records > //for one person, all with the same AKA - one per overlapped > pedigree. ALIAS is unique on > //id/people_id. > CommonTableExpression<Record2<String, UUID>> aka = > name("aka").fields("aka","aid") > .as(selectDistinct(ALIAS.AKA, ALIAS.PERSON_ID) > .from(ALIAS) > .where(ALIAS.AKA.in(pedIndivMap.keySet()))); > > Map<String, PersonRecord> fullMap = new HashMap<>(); > fullMap.putAll(ctx.with(aka) > .select() > .from(PERSON) > .join(aka).on(aka.field("aid").cast(UUID.class).equal(PERSON.ID > )) > .fetchMap(aka.field("aka").cast(String.class), r -> > r.into(PERSON))); > return fullMap; > } > > The need to cast the fields of the CTE have me thinking I've done > something incorrectly. > Sheer paranoia? > > rjs > > -- > 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/94c55220-f025-c4b5-093e-8101f85bc527%40gmail.com > <https://groups.google.com/d/msgid/jooq-user/94c55220-f025-c4b5-093e-8101f85bc527%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7jvTVgfy9J8jG_YL2%2BHOAFA12fdNsRXjKHcuygpiCF-w%40mail.gmail.com.
