如何用QueryDsl实现按codeRecurrency去重并支持分页的JPQL查询

Got it, let's tackle this problem. The root issue here is that your join with RecurrencyDetailsEntity creates a cartesian product—one RecurrencyEntity maps to multiple RecurrencyDetailsEntity records, leading to duplicate codeRecurrency entries in your result. Since you need to maintain query-level pagination (post-list deduplication breaks page counts and offsets), we need to resolve this directly in the QueryDSL logic.

Here are three actionable solutions, ordered by compatibility and practicality:

Solution 1: Subquery for Unique codeRecurrency + Filter for Latest Detail (Cross-Database Compatible) #

This approach is the most universal, working across all databases. We first fetch a paginated list of unique codeRecurrency values, then query the full VO data for those codes, ensuring each code only returns the latest related RecurrencyDetailsEntity record.

Step 1: Build a Subquery for Unique codeRecurrency #

First, create a subquery that gets all valid, distinct codeRecurrency values matching your filters:

public JPQLQuery<RecurrenceErrorVO> getQueryErrorRecurrence(Integer companyCode, Collection<SearchModelDTO<Object>> searchModelDTOs) {
    //Entities
    QRecurrencyEntity qRecurrencyEntity = QRecurrencyEntity.recurrencyEntity;
    QSubscriptionEntity qSubscriptionEntity = QSubscriptionEntity.subscriptionEntity;
    QRecurrencyDetailsEntity qRecurrencyDetailsEntity = QRecurrencyDetailsEntity.recurrencyDetailsEntity;
    QSubcriptionProductEntity qSubcriptionProductEntity = QSubcriptionProductEntity.subcriptionProductEntity;
    QArticleEntity qArticleEntity = QArticleEntity.articleEntity;
    QCatalogoValorDTO qStatusGeneral = new QCatalogoValorDTO("qStatusGeneral");
    QCatalogoValorDTO qrecurrenceType = new QCatalogoValorDTO("qrecurrenceType");
    QCatalogoValorDTO qbusinessTypeDTO = new QCatalogoValorDTO("qbusinessTypeDTO");
    QCatalogoValorDTO qcausalCatalogType = new QCatalogoValorDTO("qcausalCatalogType");

    BooleanBuilder where = new BooleanBuilder();
    where.and(qRecurrencyEntity.statusGeneralValue.ne(SirConstants.RECURRENCE_STATUS_FIN));
    where.and(qRecurrencyEntity.statusGeneralValue.ne(SirConstants.RECURRENCE_STATUS_SIN_CUPO));
    where.and(qRecurrencyEntity.statusGeneralValue.ne(SirConstants.RECURRENCE_STATUS_FIN_WITH_OBSERVATION));
    where.and(qRecurrencyDetailsEntity.statusRecurrencyDetailsValue.ne(SirConstants.ZERO.toString()));
    where.and(qRecurrencyEntity.originRecurrenceValue.eq(SirConstants.RECURRENCE_ORIGIN_INTERNO));
    where.and(qRecurrencyEntity.companyCode.eq(companyCode));
    where.and(qRecurrencyEntity.status.isTrue());
    SearchModelUtil.addDynamicWhere(searchModelDTOs, where, RecurrencyEntity.class, "recurrencyEntity");

    // Subquery to get unique codeRecurrency values
    JPQLQuery<String> uniqueCodeSubQuery = from(qRecurrencyEntity)
        .select(qRecurrencyEntity.codeRecurrency)
        .distinct()
        .leftJoin(qRecurrencyEntity.recurrencyDetailsEntity, qRecurrencyDetailsEntity)
        .where(where)
        .orderBy(qRecurrencyEntity.codeRecurrency.desc());

    // Main query: Fetch VO data for unique codes, only taking the latest detail per code
    JPQLQuery<RecurrenceErrorVO> query = from(qRecurrencyEntity)
        .select(Projections.bean(RecurrenceErrorVO.class,
            qRecurrencyEntity.codeRecurrency.as("codeRecurrence"),
            qRecurrencyDetailsEntity.codeDetailsTransaction.as("codeRecurrenceDetail"),
            qRecurrencyEntity.statusGeneralValue.as("statusGeneralValue"),
            qRecurrencyEntity.processDate.as("dateRecurrence"),
            qRecurrencyDetailsEntity.transactionDate.as("dateTransaction"),
            qSubscriptionEntity.contractIdentifier,
            qSubcriptionProductEntity.articleEntity.itemDescription.as("productName"),
            qSubscriptionEntity.numberDocumentClient.as("clientDocNumber"),
            qSubscriptionEntity.customerName.as("clientName"),
            qSubscriptionEntity.subscriptionValue.as("subscriptionValue"),
            qSubscriptionEntity.statusSubscriptionValue,
            qStatusGeneral.nombreCatalogoValor.as("statusGeneralDescription"),
            qRecurrencyEntity.originRecurrenceValue.as("originRecurrenceValue"),
            qRecurrencyEntity.value,
            qRecurrencyEntity.status,
            qRecurrencyDetailsEntity.causalValue.as("codeCausalValue"),
            qRecurrencyDetailsEntity.causalType.as("codeCausalType"),
            qbusinessTypeDTO.nombreCatalogoValor.as("marca"),
            qcausalCatalogType.nombreCatalogoValor.as("causalValue"),
            qrecurrenceType.nombreCorto.as("nombreCorto"),
            qrecurrenceType.nombreCatalogoValor.as("transactionType")))
        .innerJoin(qRecurrencyEntity.recurrencyDetailsEntity, qRecurrencyDetailsEntity)
        .leftJoin(qRecurrencyEntity.subscriptionEntity, qSubscriptionEntity)
        .leftJoin(qRecurrencyEntity.subcriptionProductEntity, qSubcriptionProductEntity)
        .innerJoin(qSubcriptionProductEntity.articleEntity, qArticleEntity)
        .innerJoin(qRecurrencyEntity.statusGeneral, qStatusGeneral)
        .innerJoin(qSubcriptionProductEntity.businessTypeDTO, qbusinessTypeDTO)
        .innerJoin(qRecurrencyDetailsEntity.recurrenceType, qrecurrenceType)
        .innerJoin(qRecurrencyDetailsEntity.causalCatalogType, qcausalCatalogType)
        // Filter to only include our unique paginated codes
        .where(qRecurrencyEntity.codeRecurrency.in(uniqueCodeSubQuery))
        // Ensure we only get the latest detail record per codeRecurrency
        .where(qRecurrencyDetailsEntity.codeDetailsTransaction.eq(
            from(qRecurrencyDetailsEntity)
                .select(qRecurrencyDetailsEntity.codeDetailsTransaction.max())
                .where(qRecurrencyDetailsEntity.recurrencyEntity.codeRecurrency.eq(qRecurrencyEntity.codeRecurrency))
        ))
        .orderBy(qRecurrencyEntity.codeRecurrency.desc());

    return query;
}

Step 2: Adjust Pagination Logic #

Modify your pagination method to apply pagination to the subquery first, then fetch the corresponding VO data:

private PageResultVO<RecurrenceErrorVO> findPagedRE(JPQLQuery<RecurrenceErrorVO> query, Pageable pageable) {
    // Extract the unique code subquery from the main query's where clause
    SubQueryExpression<?> subQueryExpr = (SubQueryExpression<?>) query.getMetadata().getWhere().getExpressions().get(0);
    JPQLQuery<String> uniqueCodeSubQuery = (JPQLQuery<String>) subQueryExpr.getSubQuery();
    
    // Apply pagination to the unique code subquery
    JPQLQuery<String> pagedCodesQuery = getQuerydsl().applyPagination(pageable, uniqueCodeSubQuery);
    List<String> pagedCodes = pagedCodesQuery.fetch();
    
    // Fetch VO data for the paginated codes
    List<RecurrenceErrorVO> list = query.where(qRecurrencyEntity.codeRecurrency.in(pagedCodes)).fetch();
    
    // Get total count from the original unique subquery
    long total = uniqueCodeSubQuery.fetchCount();
    
    return new PageResultVO<>(list, pageable, total);
}

This ensures pagination is calculated based on unique codeRecurrency values, so your page numbers and total counts are accurate.

Solution 2: Use DISTINCT ON (PostgreSQL-Specific) #

If you're using PostgreSQL, QueryDSL supports the DISTINCT ON clause, which lets you deduplicate rows based on a specific field (here, codeRecurrency) while keeping the first row from each group based on your sort order.

Modify your main query to add distinctOn:

JPQLQuery<RecurrenceErrorVO> query = from(qRecurrencyDetailsEntity)
    .select(Projections.bean(RecurrenceErrorVO.class,
        // ... your existing projections ...
    ))
    .distinctOn(qRecurrencyEntity.codeRecurrency) // Deduplicate by codeRecurrency
    // ... your existing joins and where clauses ...
    .orderBy(qRecurrencyEntity.codeRecurrency.desc(), qRecurrencyDetailsEntity.codeDetailsTransaction.desc());

This is simpler but tied to PostgreSQL. The sort order is critical here—DISTINCT ON will keep the first row in each sorted group, so we sort by codeRecurrency first, then by codeDetailsTransaction to get the latest detail per code.

Solution 3: Group By codeRecurrency (Limited Compatibility) #

If your database allows flexible grouping (e.g., MySQL with ONLY_FULL_GROUP_BY disabled), you can group by codeRecurrency and use aggregate functions for fields from RecurrencyDetailsEntity:

JPQLQuery<RecurrenceErrorVO> query = from(qRecurrencyEntity)
    .select(Projections.bean(RecurrenceErrorVO.class,
        qRecurrencyEntity.codeRecurrency.as("codeRecurrence"),
        // Use MAX to get the latest detail transaction code
        qRecurrencyDetailsEntity.codeDetailsTransaction.max().as("codeRecurrenceDetail"),
        qRecurrencyEntity.statusGeneralValue.as("statusGeneralValue"),
        qRecurrencyEntity.processDate.as("dateRecurrence"),
        // Use MAX to get the latest transaction date
        qRecurrencyDetailsEntity.transactionDate.max().as("dateTransaction"),
        // ... other fields that are 1:1 with codeRecurrency ...
    ))
    .leftJoin(qRecurrencyEntity.recurrencyDetailsEntity, qRecurrencyDetailsEntity)
    // ... your existing joins and where clauses ...
    .groupBy(qRecurrencyEntity.codeRecurrency)
    .orderBy(qRecurrencyEntity.codeRecurrency.desc());

Note: This will only work if all non-aggregated fields are functionally dependent on codeRecurrency. Most strict databases (like PostgreSQL with default settings) will reject this query unless every non-aggregated field is included in the GROUP BY clause.

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