use of org.pmiops.workbench.model.SearchGroup 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);
}
use of org.pmiops.workbench.model.SearchGroup 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;
}
use of org.pmiops.workbench.model.SearchGroup 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());
}
use of org.pmiops.workbench.model.SearchGroup 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());
}
use of org.pmiops.workbench.model.SearchGroup in project workbench by all-of-us.
the class SearchRequests method searchRequest.
private static SearchRequest searchRequest(SearchGroupItem searchGroupItem) {
SearchGroup searchGroup = new SearchGroup();
searchGroup.setId("id2");
searchGroup.addItemsItem(searchGroupItem);
SearchRequest request = new SearchRequest();
request.addIncludesItem(searchGroup);
return request;
}
Aggregations