distinct and fetchMap

[Rob Sargent]

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

[Lukas Eder]

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

--
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/94c55220-f025-c4b5-093e-8101f85bc527%40gmail.com.

[Rob Sargent]

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.