I want to use Jooq query for getting main informations and sum of subselect

Question

I need to use SUM aggregate function in select statement, without joining and grouping statements.

Here is part of my code:

dslContext.select(
                T_DAILY_DIAGRAM.DAILY_DIAGRAM_ID.convertFrom(DailyDiagramId::of),
                T_DAILY_DIAGRAM.BALANCING_GROUP_ID.convertFrom(BalancingGroupId::of),
                T_DAILY_DIAGRAM.DIAGRAM_DATE,
                row(
                        T_DAILY_DIAGRAM_REVISION.DAILY_DIAGRAM_REVISION_ID.convertFrom(DailyDiagramRevisionId::of),
                        T_DAILY_DIAGRAM_REVISION.REVISION_NUMBER,
                        T_DAILY_DIAGRAM_REVISION.PROPAGATION_STATUS.convertFrom(DailyDiagramRevisionPropagationStatus::valueOf),
                        multiset(
                               select(
                                       sum(T_DAILY_DIAGRAM_PERIOD.PRODUCTION).as("production")
                               ).from(T_DAILY_DIAGRAM_PERIOD).where(T_DAILY_DIAGRAM_PERIOD.FK_DAILY_DIAGRAM_REVISION_ID.eq(T_DAILY_DIAGRAM_REVISION.ID))
                        ).convertFrom(p -> p == null || p.isEmpty() ? null : (BigDecimal) p.getValue(0, "production")),
                        row(
                                T_DAILY_DIAGRAM_REVISION.CREATED_AT,
                                T_DAILY_DIAGRAM_REVISION.CREATED_BY,
                                T_DAILY_DIAGRAM_REVISION.UPDATED_AT,
                                T_DAILY_DIAGRAM_REVISION.UPDATED_BY
                        ).convertFrom(Records.mapping(MetadataResponse::newWithoutVersion))
                ).convertFrom(Records.mapping(DailyDiagramRevisionListResponse::new)),
                row(
                        T_DAILY_DIAGRAM.CREATED_AT,
                        T_DAILY_DIAGRAM.CREATED_BY,
                        T_DAILY_DIAGRAM.UPDATED_AT,
                        T_DAILY_DIAGRAM.UPDATED_BY,
                        T_DAILY_DIAGRAM.VERSION
                ).convertFrom(Records.mapping(MetadataResponse::newWithoutCA))
        )
                .from(T_DAILY_DIAGRAM)
                .join(T_DAILY_DIAGRAM_REVISION).on(T_DAILY_DIAGRAM_REVISION.FK_DAILY_DIAGRAM_ID.eq(T_DAILY_DIAGRAM.ID).and(T_DAILY_DIAGRAM_REVISION.CLOSING_TIME.isNull()))
                .where(
                        T_DAILY_DIAGRAM.BALANCING_GROUP_ID.eq(balancingGroupId.id().get())
                        .and(T_DAILY_DIAGRAM.DIAGRAM_DATE.between(startDate, endDate)))
                .fetch(Records.mapping(DailyDiagramListResponse::new));

This solution works for me, but it is ugly solution.

Answer 1

MULTISET is so awesome, people want to use it everywhere. But in this particular case, I guess a simple scalar, correlated subquery will suffice (just like in ordinary SQL)

field(
    select(sum(T_DAILY_DIAGRAM_PERIOD.PRODUCTION))
    .from(T_DAILY_DIAGRAM_PERIOD)
    .where(T_DAILY_DIAGRAM_PERIOD.FK_DAILY_DIAGRAM_REVISION_ID
        .eq(T_DAILY_DIAGRAM_REVISION.ID))
).as("production")

Note that starting from jOOQ 3.19, there's a new feature called implicit path correlation, which will simplify this sort of subquery by letting you omit the WHERE clause for the correlation:

field(
    select(sum(T_DAILY_DIAGRAM_REVISION.tDailyDiagramPeriod().PRODUCTION))
    .from(T_DAILY_DIAGRAM_REVISION.tDailyDiagramPeriod())
).as("production")