Correlation over derived table in jOOQ

165
January 01, 2021, at 11:40 PM

I have a simple parent - child (one to many related) tables. Here is a simplified example where I'm tracking trains and their locations.

Table TRAIN
    - ID
    - NAME
Table TRAIN_STATUS
    - ID
    - TRAIN_ID (fk)
    - STATION_CD
    - ARRIVAL_TIME 

Every time a train reached a station, a new record will be inserted in TRAIN_STATUS with the corresponding STATION_CD and ARRIVAL_TIME (timestamp)

Given a train id, I want to be able to tell what was the last known station of the train:

The SQL solution: - using a combination of derived table and correlated sub-query

select t.ID, t.name, recent.STATION_CD
from TRAIN t
join (
        select TRAIN_ID, STATION_CD
        from TRAIN_STATUS ts
        where ts.ARRIVAL_TIME = (
            select MAX(ARRIVAL_TIME) from TRAIN_STATUS where TRAIN_STATUS.TRAIN_ID = ts.TRAIN_ID
        )
) recent on t.ID=recent.TRAIN_ID
where t.ID = 1;

I couldn't put together an equivalent in jOOQ. Any help will be much appreciated.

EDIT: Dialect is MySQL (5.7). Posting my own answer @LukasEder's advise.

Answer 1

You can simplify your sql solution using analytical function as follows and use in the jooq:

select ID, name, station_cd from
(select t.ID, t.name, ts.STATION_CD, 
        row_number() over (partition by t.TRAIN_ID order by ts.ARRIVAL_TIME desc) as rn
  from train t join TRAIN_STATUS ts on t.TRAIN_ID = ts.TRAIN_ID) t
where rn = 1
Answer 2
TrainStatus ts = TRAIN_STATUS.as("TS");
SelectConditionStep<Record1<LocalDateTime>> lastArrivalTime = dslContext
        .select(max(TRAIN_STATUS.ARRIVAL_TIME))
        .from(TRAIN_STATUS)
        .where(TRAIN_STATUS.TRAIN_ID.eq(st.TRAIN_ID));
Table<Record2<Long, String>> recent = dslContext
        .select(ts.field(TRAIN_STATUS.TRAIN_ID), ts.field(TRAIN_STATUS.STATUS_CD))
        .from(ts)
        .where(ts.field(TRAIN_STATUS.ARRIVAL_TIME).eq(lastArrivalTime))
        .asTable("recent");
return dslContext
        .select(
                TRAIN.TRAIN_ID.as("id"),
                recent.field(TRAIN_STATUS.STATUS_CD).as("recent_status_code")
        )
        .from(TRAIN)
        .join(recent).on(TRAIN.TRAIN_ID.eq(recent.field(TRAIN_STATUS.TRAIN_ID)))
        .where(TRAIN.ID.eq(1)) 
        .fetchOneInto(TrainStatusModel.class);
Rent Charter Buses Company
READ ALSO
Regex, proper escaping for \" (groovy)

Regex, proper escaping for \" (groovy)

My second day coding java/groovy so bare with me :)

213
How to create a nested array from current array in PHP?

How to create a nested array from current array in PHP?

I am trying to read the categories in an XML file from my API providerThe only problem is that they nested the subcategories in a string:

186
Configuring sqlalchemy engine with user containing &ldquo;:&rdquo;

Configuring sqlalchemy engine with user containing “:”

I am trying to configure an engine in sqlalchemy to connect with temporary credentials from an AWS IAM role using get_cluster_credentials apiWhen I do so this is the user I get 'IAM:user_rw'

214
Transform is not applied on Embedded SVGs Chrome

Transform is not applied on Embedded SVGs Chrome

I am new to using SVGs, but everything was fine until I tried my code on chrome and found that it doesn't work properly, all transform attributes are neglectedFirefox gets it done as expected

40