Examples with SearchGroupItem org.pmiops.workbench.model.SearchGroupItem used on opensource projects

Example 1 with SearchGroupItem

use of org.pmiops.workbench.model.SearchGroupItem in project workbench by all-of-us.

the class CohortBuilderControllerTest method createSearchRequests.

private SearchRequest createSearchRequests(String type, List<SearchParameter> parameters) {
    final SearchGroupItem searchGroupItem = new SearchGroupItem().type(type).searchParameters(parameters);
    final SearchGroup searchGroup = new SearchGroup().addItemsItem(searchGroupItem);
    List<SearchGroup> groups = new ArrayList<>();
    groups.add(searchGroup);
    return new SearchRequest().includes(groups);
}

Example 2 with SearchGroupItem

use of org.pmiops.workbench.model.SearchGroupItem in project workbench by all-of-us.

the class ParticipantCounter method buildQuery.

private StringJoiner buildQuery(List<SearchGroup> groups, String mainTable, Map<String, QueryParameterValue> params, Boolean excludeSQL) {
    StringJoiner joiner = new StringJoiner("and ");
    List<String> queryParts = new ArrayList<>();
    for (SearchGroup includeGroup : groups) {
        for (SearchGroupItem includeItem : includeGroup.getItems()) {
            QueryJobConfiguration queryRequest = QueryBuilderFactory.getQueryBuilder(FactoryKey.getType(includeItem.getType())).buildQueryJobConfig(new QueryParameters().type(includeItem.getType()).parameters(includeItem.getSearchParameters()));
            params.putAll(queryRequest.getNamedParameters());
            queryParts.add(queryRequest.getQuery());
        }
        if (excludeSQL) {
            joiner.add(EXCLUDE_SQL_TEMPLATE.replace("${mainTable}", mainTable).replace("${excludeSql}", String.join(UNION_TEMPLATE, queryParts)));
        } else {
            joiner.add(INCLUDE_SQL_TEMPLATE.replace("${mainTable}", mainTable).replace("${includeSql}", String.join(UNION_TEMPLATE, queryParts)));
        }
        queryParts = new ArrayList<>();
    }
    return joiner;
}

Example 3 with SearchGroupItem

use of org.pmiops.workbench.model.SearchGroupItem in project workbench by all-of-us.

the class ParticipantCounterTest method buildParticipantCounterQuery.

@Test
public void buildParticipantCounterQuery() throws Exception {
    String genderNamedParameter = "";
    String conditionNamedParameter = "";
    String procedureNamedParameter = "";
    String cmConditionParameter = "";
    String procConditionParameter = "";
    String cmProcedureParameter = "";
    String procProcedureParameter = "";
    SearchParameter parameter1 = new SearchParameter().domain("Condition").group(false).type("ICD9").value("001.1");
    SearchParameter parameter2 = new SearchParameter().subtype("GEN").conceptId(8507L);
    SearchParameter parameter3 = new SearchParameter().domain("Procedure").group(false).type("CPT").value("001.2");
    SearchGroupItem searchGroupItem1 = new SearchGroupItem().type("ICD9").addSearchParametersItem(parameter1);
    SearchGroupItem searchGroupItem2 = new SearchGroupItem().type("DEMO").addSearchParametersItem(parameter2);
    SearchGroupItem searchGroupItem3 = new SearchGroupItem().type("CPT").addSearchParametersItem(parameter3);
    SearchGroup searchGroup1 = new SearchGroup().addItemsItem(searchGroupItem1);
    SearchGroup searchGroup2 = new SearchGroup().addItemsItem(searchGroupItem2);
    SearchGroup searchGroup3 = new SearchGroup().addItemsItem(searchGroupItem3);
    SearchRequest request = new SearchRequest().addIncludesItem(searchGroup1).addIncludesItem(searchGroup2).addExcludesItem(searchGroup3);
    QueryJobConfiguration actualRequest = participantCounter.buildParticipantCounterQuery(new ParticipantCriteria(request));
    for (String key : actualRequest.getNamedParameters().keySet()) {
        if (key.startsWith("gen")) {
            genderNamedParameter = key;
        } else if (key.startsWith("Condition")) {
            conditionNamedParameter = key;
            cmConditionParameter = "cm" + key.replace("Condition", "");
            procConditionParameter = "proc" + key.replace("Condition", "");
        } else if (key.startsWith("Procedure")) {
            procedureNamedParameter = key;
            cmProcedureParameter = "cm" + key.replace("Procedure", "");
            procProcedureParameter = "proc" + key.replace("Procedure", "");
        }
    }
    final String expectedSql = "select count(*) as count\n" + "from `${projectId}.${dataSetId}.person` person\n" + "where\n" + "person.person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.person` p\n" + "where person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.condition_occurrence` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.condition_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmConditionParameter + ",@" + procConditionParameter + ")\n" + "and b.concept_code in unnest(@" + conditionNamedParameter + ")\n" + ")\n" + ")\n" + "and person.person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.person` p\n" + "where\n" + "p.gender_concept_id in unnest(@" + genderNamedParameter + ")\n" + ")\n" + "and not exists\n" + "(select 'x' from\n" + "(select person_id\n" + "from `${projectId}.${dataSetId}.person` p\n" + "where person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.procedure_occurrence` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.procedure_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmProcedureParameter + ",@" + procProcedureParameter + ")\n" + "and b.concept_code in unnest(@" + procedureNamedParameter + ")\n" + ")\n" + ")\n" + "x where x.person_id = person.person_id)\n";
    assertEquals(expectedSql, actualRequest.getQuery());
    assertEquals("8507", actualRequest.getNamedParameters().get(genderNamedParameter).getArrayValues().get(0).getValue());
    assertEquals(parameter1.getValue(), actualRequest.getNamedParameters().get(conditionNamedParameter).getArrayValues().get(0).getValue());
}

Example 4 with SearchGroupItem

use of org.pmiops.workbench.model.SearchGroupItem in project workbench by all-of-us.

the class ParticipantCounterTest method buildParticipantCounterQuery_ExcludesWithoutIncludes.

@Test
public void buildParticipantCounterQuery_ExcludesWithoutIncludes() throws Exception {
    String genderNamedParameter = "";
    SearchParameter parameter1 = new SearchParameter().subtype("GEN").conceptId(8507L);
    SearchGroupItem searchGroupItem1 = new SearchGroupItem().type("DEMO").addSearchParametersItem(parameter1);
    SearchGroup searchGroup1 = new SearchGroup().addItemsItem(searchGroupItem1);
    SearchRequest request = new SearchRequest().addExcludesItem(searchGroup1);
    QueryJobConfiguration actualRequest = participantCounter.buildParticipantCounterQuery(new ParticipantCriteria(request));
    for (String key : actualRequest.getNamedParameters().keySet()) {
        if (key.startsWith("gen")) {
            genderNamedParameter = key;
        }
    }
    final String expectedSql = "select count(*) as count\n" + "from `${projectId}.${dataSetId}.person` person\n" + "where\n" + "person.person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.person` p\n" + "where\n" + "p.gender_concept_id in unnest(@" + genderNamedParameter + ")\n" + ")\n";
    assertEquals(expectedSql, actualRequest.getQuery());
    assertEquals("8507", actualRequest.getNamedParameters().get(genderNamedParameter).getArrayValues().get(0).getValue());
}

Example 5 with SearchGroupItem

use of org.pmiops.workbench.model.SearchGroupItem in project workbench by all-of-us.

the class CodesQueryBuilderTest method getMappedParameters.

@Test
public void getMappedParameters() throws Exception {
    final SearchParameter searchParam1 = new SearchParameter().group(false).domain("Condition").value("001");
    final SearchParameter searchParam2 = new SearchParameter().group(false).domain("Procedure").value("002");
    final SearchParameter searchParam3 = new SearchParameter().group(false).domain("Procedure").value("003");
    final SearchParameter searchParam4 = new SearchParameter().group(true).domain("Procedure").value("0");
    SearchGroupItem item = new SearchGroupItem().type("ICD9").searchParameters(Arrays.asList(searchParam1, searchParam2, searchParam3, searchParam4));
    Map<CodesQueryBuilder.GroupType, ListMultimap<String, SearchParameter>> mappedParemeters = queryBuilder.getMappedParameters(item.getSearchParameters());
    assertEquals(2, mappedParemeters.keySet().size());
    assertEquals(new HashSet<CodesQueryBuilder.GroupType>(Arrays.asList(CodesQueryBuilder.GroupType.GROUP, CodesQueryBuilder.GroupType.NOT_GROUP)), mappedParemeters.keySet());
    assertEquals(Arrays.asList(searchParam1), mappedParemeters.get(CodesQueryBuilder.GroupType.NOT_GROUP).get("Condition"));
    assertEquals(Arrays.asList(searchParam2, searchParam3), mappedParemeters.get(CodesQueryBuilder.GroupType.NOT_GROUP).get("Procedure"));
    assertEquals(Arrays.asList(searchParam4), mappedParemeters.get(CodesQueryBuilder.GroupType.GROUP).get("Procedure"));
}