Examples with WhereClauseBuilder org.sagebionetworks.bridge.hibernate.QueryBuilder.WhereClauseBuilder used on opensource projects

Example 1 with WhereClauseBuilder

use of org.sagebionetworks.bridge.hibernate.QueryBuilder.WhereClauseBuilder in project BridgeServer2 by Sage-Bionetworks.

the class HibernateAdherenceRecordDao method createQuery.

protected QueryBuilder createQuery(AdherenceRecordsSearch search) {
    QueryBuilder builder = new QueryBuilder();
    builder.append(BASE_QUERY);
    WhereClauseBuilder where = builder.startWhere(AND);
    where.appendRequired("ar.userId = :userId", "userId", search.getUserId());
    where.appendRequired("ar.studyId = :studyId", "studyId", search.getStudyId());
    // same ID
    if (!search.getAssessmentIds().isEmpty()) {
        where.append("tm.assessmentId IN :assessmentIds", "assessmentIds", search.getAssessmentIds());
    }
    if (!search.getSessionGuids().isEmpty()) {
        where.append("tm.sessionGuid IN :sessionGuids", "sessionGuids", search.getSessionGuids());
    }
    if (!search.getInstanceGuids().isEmpty()) {
        where.append("ar.instanceGuid IN :instanceGuids", "instanceGuids", search.getInstanceGuids());
    }
    if (!search.getTimeWindowGuids().isEmpty()) {
        where.append("tm.timeWindowGuid IN :timeWindowGuids", "timeWindowGuids", search.getTimeWindowGuids());
    }
    if (FALSE.equals(search.getIncludeRepeats())) {
        // This only works on records that have startedOn values...declined records can have a null
        // startedOn and will not appear in a search with includeRepeats=false.
        // userId has already been set above
        where.append("ar.startedOn = (SELECT startedOn FROM " + "AdherenceRecords WHERE userId = :userId AND " + "instanceGuid = ar.instanceGuid ORDER BY startedOn " + search.getSortOrder() + " LIMIT 1)");
    }
    where.alternativeMatchedPairs(search.getInstanceGuidStartedOnMap(), "gd", "ar.instanceGuid", "ar.startedOn");
    where.alternativeMatchedPairs(search.getEventTimestamps(), "evt", "tm.sessionStartEventId", "ar.eventTimestamp");
    where.adherenceRecordType(search.getAdherenceRecordType());
    if (search.getStartTime() != null) {
        where.append("ar.startedOn >= :startTime", "startTime", search.getStartTime().getMillis());
    }
    if (search.getEndTime() != null) {
        where.append("ar.startedOn <= :endTime", "endTime", search.getEndTime().getMillis());
    }
    builder.append("ORDER BY ar.startedOn " + search.getSortOrder().name());
    return builder;
}

Example 2 with WhereClauseBuilder

use of org.sagebionetworks.bridge.hibernate.QueryBuilder.WhereClauseBuilder in project BridgeServer2 by Sage-Bionetworks.

the class QueryBuilderTest method whereClause_orPredicate.

@Test
public void whereClause_orPredicate() {
    QueryBuilder builder = new QueryBuilder();
    WhereClauseBuilder where = builder.startWhere(OR);
    where.appendRequired("appId = :appId", "appId", "appIdValue");
    where.appendRequired("studyId = :studyId", "studyId", "studyIdValue");
    where.adherenceRecordType(SESSION);
    where.dataGroups(USER_DATA_GROUPS, "IN");
    assertEquals(builder.getQuery(), "WHERE appId = :appId AND studyId = " + ":studyId AND (tm.assessmentGuid IS NULL OR (:IN1 IN elements(" + "acct.dataGroups) AND :IN2 IN elements(acct.dataGroups)))");
}

Example 3 with WhereClauseBuilder

use of org.sagebionetworks.bridge.hibernate.QueryBuilder.WhereClauseBuilder in project BridgeServer2 by Sage-Bionetworks.

the class QueryBuilderTest method whereClause_enrollment.

@Test
public void whereClause_enrollment() {
    QueryBuilder builder = new QueryBuilder();
    WhereClauseBuilder where = builder.startWhere(AND);
    where.enrollment(EnrollmentFilter.ENROLLED, false);
    assertEquals(builder.getQuery(), "WHERE withdrawnOn IS NULL");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(EnrollmentFilter.WITHDRAWN, false);
    assertEquals(builder.getQuery(), "WHERE withdrawnOn IS NOT NULL");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(EnrollmentFilter.ALL, false);
    assertEquals(builder.getQuery(), "");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(null, false);
    assertEquals(builder.getQuery(), "");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(EnrollmentFilter.ENROLLED, true);
    assertEquals(builder.getQuery(), "WHERE enrollment.withdrawnOn IS NULL");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(EnrollmentFilter.WITHDRAWN, true);
    assertEquals(builder.getQuery(), "WHERE enrollment.withdrawnOn IS NOT NULL");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(EnrollmentFilter.ALL, true);
    assertEquals(builder.getQuery(), "");
    builder = new QueryBuilder();
    where = builder.startWhere(AND);
    where.enrollment(null, true);
    assertEquals(builder.getQuery(), "");
}

Example 4 with WhereClauseBuilder

use of org.sagebionetworks.bridge.hibernate.QueryBuilder.WhereClauseBuilder in project BridgeServer2 by Sage-Bionetworks.

the class QueryBuilderTest method whereClause_append.

@Test
public void whereClause_append() {
    QueryBuilder builder = new QueryBuilder();
    builder.append("SELECT * FROM TABLE");
    WhereClauseBuilder where = builder.startWhere(AND);
    where.append("foo1 = :bar1", "bar1", "baz1");
    where.append("foo2 = :bar2", "bar2", "baz2");
    builder.append("ORDER BY name");
    assertEquals(builder.getQuery(), "SELECT * FROM TABLE WHERE foo1 = :bar1 AND foo2 = :bar2 ORDER BY name");
    assertEquals(builder.getParameters().get("bar1"), "baz1");
    assertEquals(builder.getParameters().get("bar2"), "baz2");
}

Example 5 with WhereClauseBuilder

use of org.sagebionetworks.bridge.hibernate.QueryBuilder.WhereClauseBuilder in project BridgeServer2 by Sage-Bionetworks.

the class QueryBuilderTest method whereClause_phone.

@Test
public void whereClause_phone() {
    QueryBuilder builder = new QueryBuilder();
    WhereClauseBuilder where = builder.startWhere(AND);
    where.phone(INFIX, "(971) 248-6796");
    assertEquals(builder.getQuery(), "WHERE acct.phone.number LIKE :number");
    assertEquals(builder.getParameters().get("number"), "%9712486796%");
}