I'm stuck in get the query with unique row for codeRecurrence in a query.
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");
//data
JPQLQuery<RecurrenceErrorVO> query = from(qRecurrencyDetailsEntity).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")))
//join
query.leftJoin(qRecurrencyDetailsEntity.recurrencyEntity, qRecurrencyEntity)
.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)
;
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");
query.where(where);
query.orderBy(qRecurrencyEntity.codeRecurrency.desc(), qRecurrencyDetailsEntity.codeDetailsTransaction.desc());
return query;
}
this is my current code, and this returns values as the next table
CODERECURRENCY|CODEDCURRENCYDETAILS|STATUSGENERALVALUE|PROCESSDATE |TRANSACTIONDATE
--------------|--------------------|------------------|-------------------|-------------------
16202| 14510|ERR |2020-10-23 12:08:26|2020-11-27 16:53:57
16202| 14094|ERR |2020-10-23 12:08:26|2020-10-23 12:08:47
16202| 14093|ERR |2020-10-23 12:08:26|2020-10-23 12:08:41
16201| 14088|EXR |2020-10-22 23:51:58|2020-10-22 23:53:43
16201| 14087|EXR |2020-10-22 23:51:58|2020-10-22 23:53:37
16201| 14083|EXR |2020-10-22 23:51:58|2020-10-22 23:53:15
16201| 14082|EXR |2020-10-22 23:51:58|2020-10-22 23:53:09
16201| 14078|EXR |2020-10-22 23:51:58|2020-10-22 23:52:47
16201| 14077|EXR |2020-10-22 23:51:58|2020-10-22 23:52:41
16201| 14073|EXR |2020-10-22 23:51:58|2020-10-22 23:52:19
16201| 14072|EXR |2020-10-22 23:51:58|2020-10-22 23:52:13
16123| 13675|ERR |2020-10-01 17:06:17|2020-10-01 17:06:28
16050| 13511|ERR |2020-09-21 14:11:31|2020-09-21 14:11:31
16043| 13470|EXR |2020-09-16 10:04:20|2020-09-16 10:07:00
Need one a row by codeRecurrency, I used code for remove the repeats rows, but works if i convert JpqlQuery to a list and this change the result of values show in the webservice, i need use pagination.
this code shows the use of the query.
private PageResultVO<RecurrenceErrorVO> findPagedRE(JPQLQuery<RecurrenceErrorVO> query, Pageable pageable) {
List<RecurrenceErrorVO> list = Objects.requireNonNull(getQuerydsl()).applyPagination(
pageable, query).fetch();
Page<RecurrenceErrorVO> page = PageableExecutionUtils.getPage(list,
pageable,
query::fetchCount);
return new PageResultVO<>(page.getContent(), page.getPageable(), page.getTotalElements());
}
I use this code for future exposure as webservice with pagination.
What I should do?
You would need to use either aggregate functions with a group by clause (which cannot be used in conjunction with query::fetchCount in QueryDSL for JPA) or a distinct tuple with window functions (which requires custom functions to be registered). And even then you probably struggle with the entity projections in your projection. For example, you can't do MAX(someEntity) to get the entity with MAX(someEntity.id).
With plain Hibernate and QueryDSL this cannot be done, but it can be done using the Blaze-Persistence integration for QueryDSL. Using this Hibernate extension you can use window functions or use fetchCount in queries that specify a GROUP BY or HAVING clause. You could also utilise Blaze-Persistence CTE's or subquery lateral joins to create the query.