On 5/26/22 02:21, Lukas Eder wrote:
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
As you guessed I am using postgres but didn't roll a 'distinct on' solution.
/*
* We believe all current person.name values are also in alias.aka
*/
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")
.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(PERSON.ID.equal(aka.field(ALIAS.PERSON_ID)))
.fetchMap(aka.field(ALIAS.AKA), r -> r.into(PERSON)));
return fullMap;
}
Thank you, ever so much.
TL/DR: Funny thing is I've been handling duplicate appearances of a
name, but a triplicate just showed up in the latest data set. Data -
you gotta love it.
--
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/5c5f19ac-fc2c-f1c8-d51c-1753d10bafcb%40gmail.com.
