DISTINCT ON() in jOOQ

Question

I would like to make a query in PostgreSQL

select 
  distinct on(uuid) 
  (select nr_zew from bo_get_sip_cti_polaczenie_info(uuid)) as nr_zew 
from bo_sip_cti_event_day
where data_ins::date = current_date
and kierunek like 'P'
and (hangup_cause like 'NO_ANSWER' or hangup_cause like 'NO_USER_RESPONSE') 

in Java as far I have

Result<Record> result = create
    .select()
    .from("bo_sip_cti_event_day")
    .where("data_ins::date = current_date")
    .and("kierunek like 'P'")
    .and("(hangup_cause like 'NO_ANSWER' or hangup_cause like 'NO_USER_RESPONSE') ")
    .fetch();

and it works but as I try to add

Result<Record> result = create
    .selectDistinct("uuid")
    .from("bo_sip_cti_event_day")
    .where("data_ins::date = current_date")
    .and("kierunek like 'P'")
    .and("(hangup_cause like 'NO_ANSWER' or hangup_cause like 'NO_USER_RESPONSE') ")
    .fetch();

then it says that cannot do selectDistinct(String). How can I use distinct in jOOQ?

Answer 1

This is definitely non-obvious to discover. There's a SelectDistinctOnStep.distinctOn() method after you've selected your actual columns. The reason why it's non-obvious to discover is that the PostgreSQL syntax itself is a bit hard to model in an internal DSL like jOOQ's.

Think of it this way: You're selecting a set of columns (the correlated subquery) while specifying on which columns a distinctness filter should be applied:

Result<Record> result = create
  .select(field("(select nr_zew from bo_get_sip_cti_polaczenie_info(uuid))").as("nr_zew"))
  .distinctOn(field("uuid"))
  .from("bo_sip_cti_event_day")
  .where("data_ins::date = current_date")
  .and("kierunek like 'P'")
  .and("(hangup_cause like 'NO_ANSWER' or hangup_cause like 'NO_USER_RESPONSE') ")
  .fetch();

Or, if you were using the code generator:

Result<Record> result = create
  .select(field(
     select(BO_GET_SIP_CTI_POLACZENIE_INFO.NR_ZEW)
    .from(BO_GET_SIP_CTI_POLACZENIE_INFO.call(BO_SIP_CTI_EVENT_DAY.UUID))).as("nr_zew"))
  .distinctOn(BO_SIP_CTI_EVENT_DAY.UUID)
  .from(BO_SIP_CTI_EVENT_DAY)
  .where(BO_SIP_CTI_EVENT_DAY.cast(Date.class).eq(currentDate()))
  .and(BO_SIP_CTI_EVENT_DAY.KIERUNEK.like("P"))
  .and(BO_SIP_CTI_EVENT_DAY.HANGUP_CAUSE.like("NO_ANSWER")
    .or(BO_SIP_CTI_EVENT_DAY.HANGUP_CAUSE.like("NO_USER_RESPONSE")))
  .fetch();

Side-note on your usage of LIKE

Do note that the underscore (_) character is a single-character wild-card in SQL, so your LIKE predicates might not be entirely correct. Ideally, just use ordinary comparison predicates, such as:

• kierunek = 'P'
• hangup_cause IN ('NO_ANSWER', 'NO_USER_RESPONSE')

You don't really seem to need LIKE.