@Override
    public List<Build> standardFilter(Branch branch, StandardBuildFilterData data, Function<String, PropertyType<?>> propertyTypeAccessor) {
        // Query root
        StringBuilder tables = new StringBuilder("SELECT DISTINCT(B.ID) FROM BUILDS B ");
        // Criterias
        StringBuilder criteria = new StringBuilder(" WHERE B.BRANCHID = :branch");

        // Parameters
        MapSqlParameterSource params = new MapSqlParameterSource("branch", branch.id());
        Integer sinceBuildId = null;

        // sincePromotionLevel
        String sincePromotionLevel = data.getSincePromotionLevel();
        if (StringUtils.isNotBlank(sincePromotionLevel)) {
            // Gets the promotion level ID
            int promotionLevelId = structureRepository
                    .getPromotionLevelByName(branch, sincePromotionLevel)
                    .map(Entity::id)
                    .orElse(-1);
            // Gets the last build having this promotion level
            Integer id = findLastBuildWithPromotionLevel(promotionLevelId);
            if (id != null) {
                sinceBuildId = id;
            }
        }

        // withPromotionLevel
        String withPromotionLevel = data.getWithPromotionLevel();
        if (StringUtils.isNotBlank(withPromotionLevel)) {
            tables.append(
                    " LEFT JOIN PROMOTION_RUNS PR ON PR.BUILDID = B.ID" +
                            " LEFT JOIN PROMOTION_LEVELS PL ON PL.ID = PR.PROMOTIONLEVELID");
            criteria.append(" AND PL.NAME = :withPromotionLevel");
            params.addValue("withPromotionLevel", withPromotionLevel);
        }

        // afterDate
        LocalDate afterDate = data.getAfterDate();
        if (afterDate != null) {
            criteria.append(" AND B.CREATION >= :afterDate");
            params.addValue("afterDate", dateTimeForDB(afterDate.atTime(0, 0)));
        }

        // beforeDate
        LocalDate beforeDate = data.getBeforeDate();
        if (beforeDate != null) {
            criteria.append(" AND B.CREATION <= :beforeDate");
            params.addValue("beforeDate", dateTimeForDB(beforeDate.atTime(23, 59, 59)));
        }

        // sinceValidationStamp
        String sinceValidationStamp = data.getSinceValidationStamp();
        if (StringUtils.isNotBlank(sinceValidationStamp)) {
            // Gets the validation stamp ID
            int validationStampId = getValidationStampId(branch, sinceValidationStamp);
            // Gets the last build having this validation stamp and the validation status
            Integer id = findLastBuildWithValidationStamp(validationStampId, data.getSinceValidationStampStatus());
            if (id != null) {
                if (sinceBuildId == null) {
                    sinceBuildId = id;
                } else {
                    sinceBuildId = Math.max(sinceBuildId, id);
                }
            }
        }

        // withValidationStamp
        String withValidationStamp = data.getWithValidationStamp();
        if (StringUtils.isNotBlank(withValidationStamp)) {
            tables.append(
                    "  LEFT JOIN (" +
                            " SELECT R.BUILDID,  R.VALIDATIONSTAMPID, VRS.VALIDATIONRUNSTATUSID " +
                            " FROM VALIDATION_RUNS R" +
                            " INNER JOIN VALIDATION_RUN_STATUSES VRS ON VRS.ID = (SELECT ID FROM VALIDATION_RUN_STATUSES WHERE VALIDATIONRUNID = R.ID ORDER BY ID DESC LIMIT 1)" +
                            " AND R.ID = (SELECT MAX(ID) FROM VALIDATION_RUNS WHERE BUILDID = R.BUILDID AND VALIDATIONSTAMPID = R.VALIDATIONSTAMPID)" +
                            " ) S ON S.BUILDID = B.ID"
            );
            // Gets the validation stamp ID
            int validationStampId = getValidationStampId(branch, withValidationStamp);
            criteria.append(" AND (S.VALIDATIONSTAMPID = :validationStampId");
            params.addValue("validationStampId", validationStampId);
            // withValidationStampStatus
            String withValidationStampStatus = data.getWithValidationStampStatus();
            if (StringUtils.isNotBlank(withValidationStampStatus)) {
                criteria.append(" AND S.VALIDATIONRUNSTATUSID = :withValidationStampStatus");
                params.addValue("withValidationStampStatus", withValidationStampStatus);
            }
            criteria.append(")");
        }

        // withProperty
        String withProperty = data.getWithProperty();
        if (StringUtils.isNotBlank(withProperty)) {
            tables.append(" LEFT JOIN PROPERTIES PP ON PP.BUILD = B.ID");
            criteria.append(" AND PP.TYPE = :withProperty");
            params.addValue("withProperty", withProperty);
            // withPropertyValue
            String withPropertyValue = data.getWithPropertyValue();
            if (StringUtils.isNotBlank(withPropertyValue)) {
                // Gets the property type
                PropertyType<?> propertyType = propertyTypeAccessor.apply(withProperty);
                // Gets the search arguments
                PropertySearchArguments searchArguments = propertyType.getSearchArguments(withPropertyValue);
                // If defined use them
                if (searchArguments != null && searchArguments.isDefined()) {
                    PropertyJdbcRepository.prepareQueryForPropertyValue(
                            searchArguments,
                            tables,
                            criteria,
                            params
                    );
                } else {
                    // No match
                    return Collections.emptyList();
                }
            }
        }

        // sinceProperty
        String sinceProperty = data.getSinceProperty();
        if (StringUtils.isNotBlank(sinceProperty)) {
            String sincePropertyValue = data.getSincePropertyValue();
            Integer id = findLastBuildWithPropertyValue(branch, sinceProperty, sincePropertyValue, propertyTypeAccessor);
            if (id != null) {
                if (sinceBuildId == null) {
                    sinceBuildId = id;
                } else {
                    sinceBuildId = Math.max(sinceBuildId, id);
                }
            }
        }

        // linkedFrom
        String linkedFrom = data.getLinkedFrom();
        if (isNotBlank(linkedFrom)) {
            tables.append(
                    " LEFT JOIN BUILD_LINKS BLFROM ON BLFROM.TARGETBUILDID = B.ID" +
                            " LEFT JOIN BUILDS BDFROM ON BDFROM.ID = BLFROM.BUILDID" +
                            " LEFT JOIN BRANCHES BRFROM ON BRFROM.ID = BDFROM.BRANCHID" +
                            " LEFT JOIN PROJECTS PJFROM ON PJFROM.ID = BRFROM.PROJECTID"
            );
            String project = StringUtils.substringBefore(linkedFrom, ":");
            criteria.append(" AND PJFROM.NAME = :fromProject");
            params.addValue("fromProject", project);
            String buildPattern = StringUtils.substringAfter(linkedFrom, ":");
            if (StringUtils.isNotBlank(buildPattern)) {
                if (StringUtils.contains(buildPattern, "*")) {
                    criteria.append(" AND BDFROM.NAME LIKE :buildFrom");
                    params.addValue("buildFrom", StringUtils.replace(buildPattern, "*", "%"));
                } else {
                    criteria.append(" AND BDFROM.NAME = :buildFrom");
                    params.addValue("buildFrom", buildPattern);
                }
            }
            // linkedFromPromotion
            String linkedFromPromotion = data.getLinkedFromPromotion();
            if (StringUtils.isNotBlank(linkedFromPromotion)) {
                tables.append(
                        " LEFT JOIN PROMOTION_RUNS PRFROM ON PRFROM.BUILDID = BDFROM.ID" +
                                " LEFT JOIN PROMOTION_LEVELS PLFROM ON PLFROM.ID = PRFROM.PROMOTIONLEVELID"
                );
                criteria.append(" AND PLFROM.NAME = :linkedFromPromotion");
                params.addValue("linkedFromPromotion", linkedFromPromotion);
            }
        }

        // linkedTo
        String linkedTo = data.getLinkedTo();
        if (isNotBlank(linkedTo)) {
            tables.append(
                    " LEFT JOIN BUILD_LINKS BLTO ON BLTO.BUILDID = B.ID" +
                            " LEFT JOIN BUILDS BDTO ON BDTO.ID = BLTO.TARGETBUILDID" +
                            " LEFT JOIN BRANCHES BRTO ON BRTO.ID = BDTO.BRANCHID" +
                            " LEFT JOIN PROJECTS PJTO ON PJTO.ID = BRTO.PROJECTID"
            );
            String project = StringUtils.substringBefore(linkedTo, ":");
            criteria.append(" AND PJTO.NAME = :toProject");
            params.addValue("toProject", project);
            String buildPattern = StringUtils.substringAfter(linkedTo, ":");
            if (StringUtils.isNotBlank(buildPattern)) {
                if (StringUtils.contains(buildPattern, "*")) {
                    criteria.append(" AND BDTO.NAME LIKE :buildTo");
                    params.addValue("buildTo", StringUtils.replace(buildPattern, "*", "%"));
                } else {
                    criteria.append(" AND BDTO.NAME = :buildTo");
                    params.addValue("buildTo", buildPattern);
                }
            }
            // linkedToPromotion
            String linkedToPromotion = data.getLinkedToPromotion();
            if (StringUtils.isNotBlank(linkedToPromotion)) {
                tables.append(
                        " LEFT JOIN PROMOTION_RUNS PRTO ON PRTO.BUILDID = BDTO.ID" +
                                " LEFT JOIN PROMOTION_LEVELS PLTO ON PLTO.ID = PRTO.PROMOTIONLEVELID"
                );
                criteria.append(" AND PLTO.NAME = :linkedToPromotion");
                params.addValue("linkedToPromotion", linkedToPromotion);
            }
        }

        // Since build?
        if (sinceBuildId != null) {
            criteria.append(" AND B.ID >= :sinceBuildId");
            params.addValue("sinceBuildId", sinceBuildId);
        }

        // Final SQL
        String sql = format(
                "%s %s ORDER BY B.ID DESC LIMIT :count",
                tables,
                criteria);
        params.addValue("count", data.getCount());

        // Running the query
        return loadBuilds(sql, params);
    }
@ Override public List < Build > standardFilter ( Branch branch , StandardBuildFilterData data , Function < String , PropertyType < ? > > propertyTypeAccessor ) { StringBuilder tables = new StringBuilder ( "SELECT DISTINCT(B.ID) FROM BUILDS B " ) ; StringBuilder criteria = new StringBuilder ( " WHERE B.BRANCHID = :branch" ) ; MapSqlParameterSource params = new MapSqlParameterSource ( "branch" , branch . id ( ) ) ; Integer sinceBuildId = null ; String sincePromotionLevel = data . getSincePromotionLevel ( ) ; if ( StringUtils . isNotBlank ( sincePromotionLevel ) ) { int promotionLevelId = structureRepository . getPromotionLevelByName ( branch , sincePromotionLevel ) . map ( Entity :: id ) . orElse ( - 1 ) ; Integer id = findLastBuildWithPromotionLevel ( promotionLevelId ) ; if ( id != null ) { sinceBuildId = id ; } } String withPromotionLevel = data . getWithPromotionLevel ( ) ; if ( StringUtils . isNotBlank ( withPromotionLevel ) ) { tables . append ( " LEFT JOIN PROMOTION_RUNS PR ON PR.BUILDID = B.ID" + " LEFT JOIN PROMOTION_LEVELS PL ON PL.ID = PR.PROMOTIONLEVELID" ) ; criteria . append ( " AND PL.NAME = :withPromotionLevel" ) ; params . addValue ( "withPromotionLevel" , withPromotionLevel ) ; } LocalDate afterDate = data . getAfterDate ( ) ; if ( afterDate != null ) { criteria . append ( " AND B.CREATION >= :afterDate" ) ; params . addValue ( "afterDate" , dateTimeForDB ( afterDate . atTime ( 0 , 0 ) ) ) ; } LocalDate beforeDate = data . getBeforeDate ( ) ; if ( beforeDate != null ) { criteria . append ( " AND B.CREATION <= :beforeDate" ) ; params . addValue ( "beforeDate" , dateTimeForDB ( beforeDate . atTime ( 23 , 59 , 59 ) ) ) ; } String sinceValidationStamp = data . getSinceValidationStamp ( ) ; if ( StringUtils . isNotBlank ( sinceValidationStamp ) ) { int validationStampId = getValidationStampId ( branch , sinceValidationStamp ) ; Integer id = findLastBuildWithValidationStamp ( validationStampId , data . getSinceValidationStampStatus ( ) ) ; if ( id != null ) { if ( sinceBuildId == null ) { sinceBuildId = id ; } else { sinceBuildId = Math . max ( sinceBuildId , id ) ; } } } String withValidationStamp = data . getWithValidationStamp ( ) ; if ( StringUtils . isNotBlank ( withValidationStamp ) ) { tables . append ( "  LEFT JOIN (" + " SELECT R.BUILDID,  R.VALIDATIONSTAMPID, VRS.VALIDATIONRUNSTATUSID " + " FROM VALIDATION_RUNS R" + " INNER JOIN VALIDATION_RUN_STATUSES VRS ON VRS.ID = (SELECT ID FROM VALIDATION_RUN_STATUSES WHERE VALIDATIONRUNID = R.ID ORDER BY ID DESC LIMIT 1)" + " AND R.ID = (SELECT MAX(ID) FROM VALIDATION_RUNS WHERE BUILDID = R.BUILDID AND VALIDATIONSTAMPID = R.VALIDATIONSTAMPID)" + " ) S ON S.BUILDID = B.ID" ) ; int validationStampId = getValidationStampId ( branch , withValidationStamp ) ; criteria . append ( " AND (S.VALIDATIONSTAMPID = :validationStampId" ) ; params . addValue ( "validationStampId" , validationStampId ) ; String withValidationStampStatus = data . getWithValidationStampStatus ( ) ; if ( StringUtils . isNotBlank ( withValidationStampStatus ) ) { criteria . append ( " AND S.VALIDATIONRUNSTATUSID = :withValidationStampStatus" ) ; params . addValue ( "withValidationStampStatus" , withValidationStampStatus ) ; } criteria . append ( ")" ) ; } String withProperty = data . getWithProperty ( ) ; if ( StringUtils . isNotBlank ( withProperty ) ) { tables . append ( " LEFT JOIN PROPERTIES PP ON PP.BUILD = B.ID" ) ; criteria . append ( " AND PP.TYPE = :withProperty" ) ; params . addValue ( "withProperty" , withProperty ) ; String withPropertyValue = data . getWithPropertyValue ( ) ; if ( StringUtils . isNotBlank ( withPropertyValue ) ) { PropertyType < ? > propertyType = propertyTypeAccessor . apply ( withProperty ) ; PropertySearchArguments searchArguments = propertyType . getSearchArguments ( withPropertyValue ) ; if ( searchArguments != null && searchArguments . isDefined ( ) ) { PropertyJdbcRepository . prepareQueryForPropertyValue ( searchArguments , tables , criteria , params ) ; } else { return Collections . emptyList ( ) ; } } } String sinceProperty = data . getSinceProperty ( ) ; if ( StringUtils . isNotBlank ( sinceProperty ) ) { String sincePropertyValue = data . getSincePropertyValue ( ) ; Integer id = findLastBuildWithPropertyValue ( branch , sinceProperty , sincePropertyValue , propertyTypeAccessor ) ; if ( id != null ) { if ( sinceBuildId == null ) { sinceBuildId = id ; } else { sinceBuildId = Math . max ( sinceBuildId , id ) ; } } } String linkedFrom = data . getLinkedFrom ( ) ; if ( isNotBlank ( linkedFrom ) ) { tables . append ( " LEFT JOIN BUILD_LINKS BLFROM ON BLFROM.TARGETBUILDID = B.ID" + " LEFT JOIN BUILDS BDFROM ON BDFROM.ID = BLFROM.BUILDID" + " LEFT JOIN BRANCHES BRFROM ON BRFROM.ID = BDFROM.BRANCHID" + " LEFT JOIN PROJECTS PJFROM ON PJFROM.ID = BRFROM.PROJECTID" ) ; String project = StringUtils . substringBefore ( linkedFrom , ":" ) ; criteria . append ( " AND PJFROM.NAME = :fromProject" ) ; params . addValue ( "fromProject" , project ) ; String buildPattern = StringUtils . substringAfter ( linkedFrom , ":" ) ; if ( StringUtils . isNotBlank ( buildPattern ) ) { if ( StringUtils . contains ( buildPattern , "*" ) ) { criteria . append ( " AND BDFROM.NAME LIKE :buildFrom" ) ; params . addValue ( "buildFrom" , StringUtils . replace ( buildPattern , "*" , "%" ) ) ; } else { criteria . append ( " AND BDFROM.NAME = :buildFrom" ) ; params . addValue ( "buildFrom" , buildPattern ) ; } } String linkedFromPromotion = data . getLinkedFromPromotion ( ) ; if ( StringUtils . isNotBlank ( linkedFromPromotion ) ) { tables . append ( " LEFT JOIN PROMOTION_RUNS PRFROM ON PRFROM.BUILDID = BDFROM.ID" + " LEFT JOIN PROMOTION_LEVELS PLFROM ON PLFROM.ID = PRFROM.PROMOTIONLEVELID" ) ; criteria . append ( " AND PLFROM.NAME = :linkedFromPromotion" ) ; params . addValue ( "linkedFromPromotion" , linkedFromPromotion ) ; } } String linkedTo = data . getLinkedTo ( ) ; if ( isNotBlank ( linkedTo ) ) { tables . append ( " LEFT JOIN BUILD_LINKS BLTO ON BLTO.BUILDID = B.ID" + " LEFT JOIN BUILDS BDTO ON BDTO.ID = BLTO.TARGETBUILDID" + " LEFT JOIN BRANCHES BRTO ON BRTO.ID = BDTO.BRANCHID" + " LEFT JOIN PROJECTS PJTO ON PJTO.ID = BRTO.PROJECTID" ) ; String project = StringUtils . substringBefore ( linkedTo , ":" ) ; criteria . append ( " AND PJTO.NAME = :toProject" ) ; params . addValue ( "toProject" , project ) ; String buildPattern = StringUtils . substringAfter ( linkedTo , ":" ) ; if ( StringUtils . isNotBlank ( buildPattern ) ) { if ( StringUtils . contains ( buildPattern , "*" ) ) { criteria . append ( " AND BDTO.NAME LIKE :buildTo" ) ; params . addValue ( "buildTo" , StringUtils . replace ( buildPattern , "*" , "%" ) ) ; } else { criteria . append ( " AND BDTO.NAME = :buildTo" ) ; params . addValue ( "buildTo" , buildPattern ) ; } } String linkedToPromotion = data . getLinkedToPromotion ( ) ; if ( StringUtils . isNotBlank ( linkedToPromotion ) ) { tables . append ( " LEFT JOIN PROMOTION_RUNS PRTO ON PRTO.BUILDID = BDTO.ID" + " LEFT JOIN PROMOTION_LEVELS PLTO ON PLTO.ID = PRTO.PROMOTIONLEVELID" ) ; criteria . append ( " AND PLTO.NAME = :linkedToPromotion" ) ; params . addValue ( "linkedToPromotion" , linkedToPromotion ) ; } } if ( sinceBuildId != null ) { criteria . append ( " AND B.ID >= :sinceBuildId" ) ; params . addValue ( "sinceBuildId" , sinceBuildId ) ; } String sql = format ( "%s %s ORDER BY B.ID DESC LIMIT :count" , tables , criteria ) ; params . addValue ( "count" , data . getCount ( ) ) ; return loadBuilds ( sql , params ) ; }
The fat join query (dreaming of Neo4J) defines the following columns:
<p>
<pre>
B (BUILDS)
PR (PROMOTION_RUNS)
PL (PROMOTION_LEVELS)
S (last validation run status)
VALIDATIONSTAMPID
VALIDATIONRUNSTATUSID
PP (PROPERTIES)
BDFROM (builds linked from)
BRFROM (branches linked from)
PJFROM (projects linked from)
PLFROM (promotions linked from)
BDTO (builds linked to)
BRTO (branches linked to)
PJTO (projects linked to)
PLTO (promotions linked to)
</pre>