private static SelectConditionStep<Record1<Integer>> countSensorData(Field<Integer> datasetId) {
return DSL.selectCount()
.from(SENSORED_DATA)
.join(DATA)
.on(SENSORED_DATA.DATA.eq(DATA.ID)) // sensored_data -> data
.where(DATA.DATASET_ID.eq(datasetId))
.and(isIncludedAndNotHidden(DATA));
}
private static SelectConditionStep<Record1<Integer>> countDataOfType(
Field<Integer> datasetId, String type) {
return DSL.selectCount()
.from(DATA)
.where(DATA.DATASET_ID.eq(datasetId))
.and(isIncludedAndNotHidden(DATA))
.and(DATA.TYPE.eq(type));
}
private static SelectConditionStep<Record1<Integer>> countLayerData(Field<Integer> datasetId) {
return DSL.selectCount()
.from(LAYER)
.join(DATA)
.on(LAYER.DATA.eq(DATA.ID)) // layer -> data
.where(DATA.DATASET_ID.eq(datasetId))
.and(isIncludedAndNotHidden(DATA));
}
@Override
public Page<DatasetItem> fetchPage(
Pageable pageable,
boolean excludeEmpty,
String termFilter,
Boolean hasVectorData,
Boolean hasCoverageData,
Boolean hasLayerData,
Boolean hasSensorData) {
// Query filters.
Condition condition = DSL.trueCondition();
if (isNotBlank(termFilter)) {
condition =
condition.and(
DATASET
.IDENTIFIER
.likeIgnoreCase('%' + termFilter + '%')
.or(CSTL_USER.LOGIN.likeIgnoreCase('%' + termFilter + '%'))
.or(DATA.NAME.likeIgnoreCase('%' + termFilter + '%')));
}
if (excludeEmpty) {
condition = condition.and(countData(DATASET.ID).asField().greaterThan(0));
}
if (hasVectorData != null) {
Field<Integer> countVectorData = countDataOfType(DATASET.ID, "VECTOR").asField();
condition =
condition.and(hasVectorData ? countVectorData.greaterThan(0) : countVectorData.eq(0));
}
if (hasCoverageData != null) {
Field<Integer> countCoverageData = countDataOfType(DATASET.ID, "COVERAGE").asField();
condition =
condition.and(
hasCoverageData ? countCoverageData.greaterThan(0) : countCoverageData.eq(0));
}
if (hasLayerData != null) {
Field<Integer> countLayerData = countLayerData(DATASET.ID).asField();
condition =
condition.and(hasLayerData ? countLayerData.greaterThan(0) : countLayerData.eq(0));
}
if (hasSensorData != null) {
Field<Integer> countSensorData = countSensorData(DATASET.ID).asField();
condition =
condition.and(hasSensorData ? countSensorData.greaterThan(0) : countSensorData.eq(0));
}
// Content query.
List<DatasetItem> content =
dsl.selectDistinct(ITEM_FIELDS)
.from(DATASET)
.leftOuterJoin(CSTL_USER)
.on(CSTL_USER.ID.eq(DATASET.OWNER)) // dataset -> cstl_user
.leftOuterJoin(DATA)
.on(DATA.DATASET_ID.eq(DATASET.ID)) // dataset -> data
.where(condition)
.orderBy(JooqUtils.sortFields(pageable, ITEM_FIELDS))
.limit(pageable.getPageSize())
.offset(pageable.getOffset())
.fetchInto(DatasetItem.class);
// Total query.
Long total =
dsl.selectDistinct(DSL.countDistinct(DATASET.ID))
.from(DATASET)
.leftOuterJoin(CSTL_USER)
.on(DATASET.OWNER.eq(CSTL_USER.ID)) // dataset -> cstl_user
.leftOuterJoin(DATA)
.on(DATA.DATASET_ID.eq(DATASET.ID)) // dataset -> data
.where(condition)
.fetchOne(0, Long.class);
return new PageImpl<>(pageable, content, total);
}
