1

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.

Keerthi
  • 466
  • 6
  • 12
  • Please tag the database that you are using like SQL server, Oracle, MySql etc. – Popeye Dec 30 '20 at 05:44
  • I need to solve this in jOOQ as stated in the original post. Thanks for your suggestion though. – Keerthi Dec 30 '20 at 06:06
  • @Keerthi: In most RDBMS, there are better ways to solve this than how you're planning to (i.e. avoiding the 2x accessing the `TRAIN_STATUS` table). So, posting the dialect would definitely provide better answers... What jOOQ query have you tried? Why did you fail? – Lukas Eder Dec 30 '20 at 16:58
  • @LukasEder I simply couldn't think and put the solution down in jOOQ parlance. But that was yesterday. I gave it another shot after a good break and nailed it. Posting it in the original post. I understand the solution may not be the most optimal but I deliberately wanted it to be based on derived table and correlated sub-query - you know I'm learning how to use them at the moment :) Of course I would love to hear the most optimal way to solve it. – Keerthi Dec 31 '20 at 03:41
  • @Keerthi: Makes sense. You know you can provide an answer to your own questions here on Stack Overflow. That way, for future visitors of this question, it may be easier to follow your progress and solution here. For MySQL 5.7, without support for analytical functions, and other means, I think you've already found the best approach. – Lukas Eder Dec 31 '20 at 10:12

2 Answers2

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
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • 2
    Thanks for this. I will revisit this (and try to it in jOOQ) when I have mastered analytical functions. – Keerthi Dec 31 '20 at 03:49
1
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);
Keerthi
  • 466
  • 6
  • 12