如何用QueryDSL实现CHANGESET关联单条最新DECISION记录（LIMIT 1）

Hey there! Let's work through how to implement this logic with QueryDSL—fetching each CHANGESET along with its most recent DECISION record (sorted by DECISIONTIMESTAMP descending). I’ll also fix a small gap in your original SQL example to ensure we get one latest decision per changeset, not just a single record overall.

First, let's align on the core goal: for every CHANGESET, we want to join its newest associated DECISION. The most reliable way to do this (especially for scalability) is using a window function to partition decisions by their parent CHANGESETID, then filter for only the top record in each partition.

Step 1: Set Up Your QueryDSL Entity Paths #

Assuming you’ve generated QueryDSL metamodel classes for your entities (if not, you’ll need to run the QueryDSL annotation processor first):

QChangeset ch = QChangeset.changeset;
QChangesetDecision chd = QChangesetDecision.changesetDecision;

Step 2: Build the Subquery for Latest Decisions #

We’ll use row_number() as a window function to rank decisions within each CHANGESETID group, ordered by timestamp descending. Then we’ll filter to keep only the top-ranked (latest) decision per group:

// Subquery to fetch only the latest decision for each changeset
SubQueryExpression<ChangesetDecision> latestDecisionSubquery = JPAExpressions
    .selectFrom(chd)
    .where(
        JPQLExpressions.rowNumber()
            .over()
            .partitionBy(chd.changesetId) // Group decisions by their parent changeset
            .orderBy(chd.decisionTimestamp.desc()) // Sort newest first
            .eq(1) // Keep only the top-ranked (latest) decision in each group
    );

Step 3: Join with CHANGESET and Fetch Results #

Now we’ll left join this filtered subquery with the CHANGESET table to get each changeset paired with its latest decision (if one exists):

// Fetch results as Tuples (contains all fields from both tables)
List<Tuple> results = queryFactory
    .select(ch.all(), chd.all())
    .from(ch)
    .leftJoin(chd).on(ch.id.eq(chd.changesetId))
    .where(chd.in(latestDecisionSubquery))
    .fetch();

Optional: Fetch into a Custom DTO #

If you’d rather work with a dedicated DTO (like ChangesetWithLatestDecision) instead of raw Tuples, adjust the select() clause to project directly into your DTO:

List<ChangesetWithLatestDecision> dtoResults = queryFactory
    .select(Projections.constructor(
        ChangesetWithLatestDecision.class,
        ch.id,
        ch.changeDescription, // Add your CHANGESET fields here
        chd.decisionId,
        chd.decisionTimestamp,
        chd.decisionNotes // Add your DECISION fields here
    ))
    .from(ch)
    .leftJoin(chd).on(ch.id.eq(chd.changesetId))
    .where(chd.in(latestDecisionSubquery))
    .fetch();

A Note on Your Original SQL #

Your initial SQL uses ROWNUM = 1 which would only return one DECISION record across all changesets—not one per changeset. The window function approach above fixes this by partitioning on CHANGESETID, ensuring we get the latest decision for every individual changeset.

If you absolutely need to replicate the nested ROWNUM approach (though it’s less efficient for large datasets), you could use a correlated subquery that fetches the top decision per changeset:

// Correlated subquery (runs once per changeset—less efficient for large data)
SubQueryExpression<ChangesetDecision> correlatedSubquery = JPAExpressions
    .selectFrom(chd)
    .orderBy(chd.decisionTimestamp.desc())
    .where(chd.changesetId.eq(ch.id))
    .limit(1);

List<Tuple> results = queryFactory
    .select(ch.all(), correlatedSubquery)
    .from(ch)
    .fetch();

Just keep in mind this can slow down as your dataset grows, so the window function method is the better practice.

内容的提问来源于stack exchange，提问作者JiKra