How does one represent a self-join in jOOQ?

I have the following schema (SQLite) for capturing messages from messengers:

create table if not exists spot_message
(
    id             unsigned big int primary key not null,
    messenger_id   text not null,
    messenger_name text not null,
    message_type   text not null,
    timestamp      text not null,
    latitude       real not null,
    longitude      real not null
);

I use the following self-join in order to find the latest message from every sender:

select t1.*
from spot_message t1
         join (select messenger_id, max(timestamp) timestamp
               from spot_message
               group by messenger_id) t2 on t1.messenger_id = t2.messenger_id and t1.timestamp = t2.timestamp;

It is not clear to me how to represent this in jOOQ.

I currently have:

DSL.using(c.get())
    .select(asterisk())
    .from(table("spot_message").as("t1"))
    .join(select(field("messenger_id"), max(field("timestamp"))).from(table("spot_message"))
        .groupBy(field("messenger_id")))
        .on(field("messenger_id")
                .eq(field("messenger_id"))
                .and(field("timestamp")
                        .eq(field("timestamp"))));

But it is not clear how I express the "as" for the table name of the joined table ("t2").

Solution

Answering your question

To alias a Select as a derived table, you can either use:

You're using the plain SQL API for query building, so you can just use any SQL expression in your strings, such as e.g. "t2.messenger_id". This should work:

DSL.using(c.get())
    .select(asterisk())
    .from(table("spot_message").as("t1"))
    .join(select(field("messenger_id"), max(field("timestamp")))
        .from(table("spot_message"))
        .groupBy(field("messenger_id")).asTable("t2")) // t2 alias here
    .on(field("t1.messenger_id") // Qualifications here, and below
        .eq(field("t2.messenger_id"))
    .and(field("t1.timestamp")
        .eq(field("t2.timestamp"))));

However, this would be a bit more readable if you were using code generation, which I recommend for various reasons

SpotMessage t1 = SPOT_MESSAGE.as("t1");
SpotMessage t2 = SPOT_MESSAGE.as("t2");

DSL.using(c.get())
   .select(t1.asterisk())
   .from(t1)
   .join(
       select(t2.MESSENGER_ID, max(t2.TIMESTAMP).as(t2.TIMESTAMP))
       .from(t2)
       .groupBy(t2.MESSENGER_ID).asTable(t2))
   .on(t1.MESSENGER_ID.eq(t2.MESSENGER_ID))
   .and(t1.TIMESTAMP.eq(t2.TIMESTAMP));

Alternative using window functions

Since you're using SQLite, which supports window functions, why not just use those? You can even use the QUALIFY syntax, which jOOQ can emulate for you if you're using the commercial distributions:

In SQL:

select *
from spot_message
qualify timestamp = max(timestamp) over (partition by messenger_id)

In jOOQ:

ctx.selectFrom(SPOT_MESSAGE)
   .qualify(SPOT_MESSAGE.TIMESTAMP.eq(
       max(SPOT_MESSAGE.TIMESTAMP).over(partitionBy(SPOT_MESSAGE.MESSENGER_ID))
   ));

If QUALIFY isn't available to you, you can still emulate it manually by wrapping the query in a derived table:

SELECT *
FROM (
  SELECT 
    spot_message.*, 
    timestamp = max(timestamp) over (partition by messenger_id) AS is_max
  FROM spot_message
) AS t
WHERE is_max