all-of-us · SrushtiGangireddy · Jun 22, 2018 · Jun 14, 2018 · Jun 14, 2018 · Jun 14, 2018
diff --git a/api/db-cdr/generate-cdr/make-bq-data.sh b/api/db-cdr/generate-cdr/make-bq-data.sh
@@ -184,9 +184,10 @@ bq --quiet --project=$BQ_PROJECT query --nouse_legacy_sql \
 set c.source_count_value = r.source_count_value,c.count_value=r.count_value
 from  (select cast(r.stratum_1 as int64) as concept_id , sum(r.count_value) as count_value , sum(r.source_count_value) as source_count_value
 from \`$WORKBENCH_PROJECT.$WORKBENCH_DATASET.achilles_results\` r
-where r.analysis_id = 3000 and CAST(r.stratum_1 as int64) > "0" group by r.stratum_1) as r
+where r.analysis_id in (3000,2,4,5) and CAST(r.stratum_1 as int64) > "0" group by r.stratum_1) as r
 where r.concept_id = c.concept_id"
 
+
 #Concept prevalence (based on count value and not on source count value)
 bq --quiet --project=$BQ_PROJECT query --nouse_legacy_sql \
 "Update  \`$WORKBENCH_PROJECT.$WORKBENCH_DATASET.concept\`

diff --git a/api/src/main/resources/client_api.yaml b/api/src/main/resources/client_api.yaml
@@ -338,13 +338,18 @@ definitions:
   StandardConceptFilter:
     type: string
     description: filter on whether standard, non-standard, or all concepts should be returned\
-    enum: &STANDARD_CONCEPT_FILTER [ALL_CONCEPTS, STANDARD_CONCEPTS, NON_STANDARD_CONCEPTS]
+    enum: &STANDARD_CONCEPT_FILTER [ALL_CONCEPTS, STANDARD_CONCEPTS, NON_STANDARD_CONCEPTS, STANDARD_OR_CODE_ID_MATCH]
 
   Domain:
     type: string
     description: filter on the domain in which concepts should be returned
     enum: &DOMAIN [OBSERVATION, PROCEDURE, DRUG, CONDITION, MEASUREMENT, DEVICE, RACE, GENDER, ETHNICITY]
 
+  MatchType:
+    type: string
+    description: match column type on concept search
+    enum: [CONCEPT_CODE, CONCEPT_ID, CONCEPT_NAME]
+
   Concept:
     description: A concept describing a type of entity (e.g. measurement, observation, procedure.)
     type: object
@@ -432,3 +437,11 @@ definitions:
         type: "array"
         items:
           $ref: "#/definitions/Concept"
+      matchType:
+        description: match column type on which concept search was successful
+        $ref: "#/definitions/MatchType"
+      standardConcepts:
+        type: "array"
+        items:
+          $ref: "#/definitions/Concept"
+        description: standard concepts associated with the matched concept
diff --git a/api/src/test/java/org/pmiops/workbench/api/ConceptsControllerTest.java b/api/src/test/java/org/pmiops/workbench/api/ConceptsControllerTest.java
@@ -92,14 +92,41 @@ public class ConceptsControllerTest {
           .countValue(1250L)
           .prevalence(0.5F);
 
+  private static final Concept CLIENT_CONCEPT_5 = new Concept()
+          .conceptId(7890L)
+          .conceptName("conceptD test concept")
+          .standardConcept(true)
+          .conceptCode("conceptE")
+          .conceptClassId("classId5")
+          .vocabularyId("V5")
+          .domainId("Condition")
+          .countValue(7890L)
+          .prevalence(0.9F);
+
+  private static final Concept CLIENT_CONCEPT_6 = new Concept()
+          .conceptId(7891L)
+          .conceptName("conceptD test concept 2")
+          .standardConcept(false)
+          .conceptCode("conceptD")
+          .conceptClassId("classId6")
+          .vocabularyId("V6")
+          .domainId("Condition")
+          .countValue(7891L)
+          .prevalence(0.1F);
+
   private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_1 =
       makeConcept(CLIENT_CONCEPT_1);
   private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_2 =
       makeConcept(CLIENT_CONCEPT_2);
   private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_3 =
           makeConcept(CLIENT_CONCEPT_3);
-  private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_4=
+  private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_4 =
           makeConcept(CLIENT_CONCEPT_4);
+  private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_5 =
+          makeConcept(CLIENT_CONCEPT_5);
+  private static final org.pmiops.workbench.cdr.model.Concept CONCEPT_6 =
+          makeConcept(CLIENT_CONCEPT_6);
+
 
   @TestConfiguration
   @Import({
@@ -213,6 +240,22 @@ public void testSearchConceptsConceptIdMatch() throws Exception {
             new SearchConceptsRequest().query("456")), CLIENT_CONCEPT_2);
   }
 
+  @Test
+  public void testSearchConceptsMatchOrder() throws Exception{
+    saveConcepts();
+    assertResults(
+            conceptsController.searchConcepts("ns", "name",
+                    new SearchConceptsRequest().query("conceptD")), CLIENT_CONCEPT_4, CLIENT_CONCEPT_5, CLIENT_CONCEPT_6);
+  }
+
+  @Test
+  public void testSearchConceptsNonStandard() throws Exception{
+    saveConcepts();
+    assertResults(
+            conceptsController.searchConcepts("ns", "name",
+                    new SearchConceptsRequest().query("conceptB")), CLIENT_CONCEPT_2);
+  }
+
   @Test
   public void testSearchConceptsStandardConcept() throws Exception {
     saveConcepts();
@@ -387,6 +430,8 @@ private void saveConcepts() {
     conceptDao.save(CONCEPT_2);
     conceptDao.save(CONCEPT_3);
     conceptDao.save(CONCEPT_4);
+    conceptDao.save(CONCEPT_5);
+    conceptDao.save(CONCEPT_6);
   }
 
   private void assertResults(ResponseEntity<ConceptListResponse> response,

diff --git a/common-api/src/main/java/org/pmiops/workbench/cdr/MySQLDialect.java b/common-api/src/main/java/org/pmiops/workbench/cdr/MySQLDialect.java
@@ -11,8 +11,10 @@ public MySQLDialect() {
     // for MySQL.
     // For some weird reason, we need to have this function use DOUBLE; see
     // https://pavelmakhov.com/2016/09/jpa-custom-function
+
     registerFunction("match", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE,
         "match(?1) against  (?2 in boolean mode)"));
+
   }
 
 }
diff --git a/common-api/src/main/java/org/pmiops/workbench/cdr/dao/ConceptDao.java b/common-api/src/main/java/org/pmiops/workbench/cdr/dao/ConceptDao.java
@@ -8,31 +8,17 @@
 
 public interface ConceptDao extends CrudRepository<Concept, Long> {
 
-    @Query(value = "select c.* " +
-            "from concept c " +
-            "where (match(c.concept_name) against(:conceptName in boolean mode) or " +
-            "match(c.concept_code) against(:conceptName in boolean mode)) and " +
-            "c.domain_id=:domain_id and c.standard_concept=:standard_concept " +
-            "order by c.count_value desc limit 25;",
-            nativeQuery = true)
-    List<Concept> findConceptLikeNameAndDomainId(@Param("conceptName") String conceptName,@Param("domain_id") String domain_id,@Param("standard_concept") String standard_concept);
+    @Query(nativeQuery=true, value="select c.* from concept c join concept_relationship cr on c.concept_id=cr.concept_id_2 " +
+            "where cr.concept_id_1=?1 and cr.relationship_id='Maps to' ")
+    List<Concept> findStandardConcepts(long concept_id);
 
-    @Query(value = "select c.* " +
-            "from concept c " +
-            "where (match(c.concept_name) against(:conceptName in boolean mode) or " +
-            "match(c.concept_code) against(:conceptName in boolean mode)) and " +
-            "c.domain_id in ('Condition','Observation','Procedure', 'Measurement', 'Drug') and " +
-            "c.standard_concept=:standard_concept " +
-            "order by c.count_value desc limit 25;",
-            nativeQuery = true)
-    List<Concept> findConceptLikeName(@Param("conceptName") String conceptName,@Param("standard_concept") String standard_concept);
 
     @Query(value = "select c.* from concept c " +
             "where c.domain_id in ('Condition','Observation','Procedure', 'Measurement', 'Drug') and " +
             "c.standard_concept=:standard_concept " +
-            "order by c.count_value desc limit 25;",
+            "order by c.count_value desc limit :maxResults ",
     nativeQuery = true)
-    List<Concept> findAllConceptsOrderedByCount(@Param("standard_concept") String standard_concept);
+    List<Concept> findAllConceptsOrderedByCount(@Param("standard_concept") String standard_concept, @Param("maxResults") long maxResults);
 
     @Query(value = "select c.* from concept c " +
             "where c.domain_id=:domain_id and " +

diff --git a/common-api/src/main/java/org/pmiops/workbench/cdr/dao/ConceptRelationshipDao.java b/common-api/src/main/java/org/pmiops/workbench/cdr/dao/ConceptRelationshipDao.java
@@ -0,0 +1,10 @@
+package org.pmiops.workbench.cdr.dao;
+
+import java.util.List;
+import org.pmiops.workbench.cdr.model.ConceptRelationship;
+import org.springframework.data.repository.CrudRepository;
+
+public interface ConceptRelationshipDao extends CrudRepository<ConceptRelationship, Long> {
+
+    List<ConceptRelationship> findAll();
+}
diff --git a/common-api/src/main/java/org/pmiops/workbench/cdr/dao/ConceptService.java b/common-api/src/main/java/org/pmiops/workbench/cdr/dao/ConceptService.java
@@ -22,7 +22,8 @@ public class ConceptService {
     public enum StandardConceptFilter {
         ALL_CONCEPTS,
         STANDARD_CONCEPTS,
-        NON_STANDARD_CONCEPTS
+        NON_STANDARD_CONCEPTS,
+        STANDARD_OR_CODE_ID_MATCH
     }
 
     @PersistenceContext(unitName = "cdr")
@@ -41,26 +42,32 @@ public static String modifyMultipleMatchKeyword(String query){
         String[] keywords = query.split("[,+\\s+]");
         for(int i = 0; i < keywords.length; i++){
             String key = keywords[i];
-            if(key.length() < 3){
+            if(key.length() < 3 && !key.isEmpty()){
                 key = "\"" + key + "\"";
                 keywords[i] = key;
             }
         }
 
-        String query2 = "";
+        StringBuilder query2 = new StringBuilder();
         for(String key : keywords){
-            if(query2.isEmpty()){
-                query2 = "+" + key;
-            }else if(key.contains("\"")){
-                query2 = query2 + key;
-            }else{
-                query2 = query2+ "+"+ key;
+            if(!key.isEmpty()){
+                if(query2.length()==0){
+                    query2.append("+");
+                    query2.append(key);
+                }else if(key.contains("\"")){
+                    query2.append(key);
+                }else{
+                    query2.append("+");
+                    query2.append(key);
+                }
             }
+
         }
-        return query2;
+        return query2.toString();
     }
 
     public static final String STANDARD_CONCEPT_CODE = "S";
+    public static final String CLASSIFICATION_CONCEPT_CODE = "C";
 
     public Slice<Concept> searchConcepts(String query,
                                          StandardConceptFilter standardConceptFilter, List<String> vocabularyIds,
@@ -74,42 +81,100 @@ public Slice<Concept> searchConcepts(String query,
                     List<Predicate> predicates = new ArrayList<>();
 
                     // Check that the concept name, code, or ID matches the query string.
-                    List<Predicate> queryPredicates = new ArrayList<>();
-                    Expression<Double> matchExp = criteriaBuilder.function("match", Double.class,
-                            root.get("conceptName"), criteriaBuilder.literal(keyword));
-                    queryPredicates.add(criteriaBuilder.greaterThan(matchExp, 0.0));
-                    queryPredicates.add(criteriaBuilder.equal(root.get("conceptCode"),
+                    List<Predicate> conceptCodeIDName = new ArrayList<>();
+
+                    conceptCodeIDName.add(criteriaBuilder.equal(root.get("conceptCode"),
                             criteriaBuilder.literal(query)));
+
                     try {
                         long conceptId = Long.parseLong(query);
-                        queryPredicates.add(criteriaBuilder.equal(root.get("conceptId"),
+                        conceptCodeIDName.add(criteriaBuilder.equal(root.get("conceptId"),
                                 criteriaBuilder.literal(conceptId)));
                     } catch (NumberFormatException e) {
                         // Not a long, don't try to match it to a concept ID.
                     }
-                    predicates.add(criteriaBuilder.or(queryPredicates.toArray(new Predicate[0])));
+
+
+                    Expression<Double> matchExp = criteriaBuilder.function("match", Double.class,
+                            root.get("conceptName"), criteriaBuilder.literal(keyword));
+
 
                     // Optionally filter on standard concept, vocabulary ID, domain ID
                     if (standardConceptFilter.equals(StandardConceptFilter.STANDARD_CONCEPTS)) {
-                        predicates.add(criteriaBuilder.equal(root.get("standardConcept"),
+                        conceptCodeIDName.add(criteriaBuilder.greaterThan(matchExp, 0.0));
+                        predicates.add(
+                                criteriaBuilder.or(
+                                        conceptCodeIDName.toArray(new Predicate[0])
+                                )
+                        );
+
+                        List<Predicate> standardConceptPredicates = new ArrayList<>();
+                        standardConceptPredicates.add(criteriaBuilder.equal(root.get("standardConcept"),
                                 criteriaBuilder.literal(STANDARD_CONCEPT_CODE)));
+                        standardConceptPredicates.add(criteriaBuilder.equal(root.get("standardConcept"),
+                                criteriaBuilder.literal(CLASSIFICATION_CONCEPT_CODE)));
+
+                        predicates.add(
+                                criteriaBuilder.or(standardConceptPredicates.toArray(new Predicate[0]))
+                        );
+
                     } else if (standardConceptFilter.equals(StandardConceptFilter.NON_STANDARD_CONCEPTS)) {
+                        conceptCodeIDName.add(criteriaBuilder.greaterThan(matchExp, 0.0));
+                        predicates.add(
+                                criteriaBuilder.or(
+                                        conceptCodeIDName.toArray(new Predicate[0])
+                                )
+                        );
+
                         List<Predicate> standardConceptPredicates = new ArrayList<>();
                         standardConceptPredicates.add(criteriaBuilder.isNull(root.get("standardConcept")));
                         standardConceptPredicates.add(criteriaBuilder.notEqual(root.get("standardConcept"),
                                 criteriaBuilder.literal(STANDARD_CONCEPT_CODE)));
-                        predicates.add(criteriaBuilder.or(
+                        standardConceptPredicates.add(criteriaBuilder.notEqual(root.get("standardConcept"),
+                                criteriaBuilder.literal(CLASSIFICATION_CONCEPT_CODE)));
+
+                        predicates.add(
+                               criteriaBuilder.or(standardConceptPredicates.toArray(new Predicate[0]))
+                        );
+
+
+                    } else if (standardConceptFilter.equals(StandardConceptFilter.STANDARD_OR_CODE_ID_MATCH)) {
+
+                        List<Predicate> conceptNameFilter = new ArrayList<>();
+                        conceptNameFilter.add(criteriaBuilder.greaterThan(matchExp, 0.0));
+
+                        List<Predicate> standardConceptPredicates = new ArrayList<>();
+                        standardConceptPredicates.add(criteriaBuilder.equal(root.get("standardConcept"),
+                                criteriaBuilder.literal(STANDARD_CONCEPT_CODE)));
+                        standardConceptPredicates.add(criteriaBuilder.equal(root.get("standardConcept"),
+                                criteriaBuilder.literal(CLASSIFICATION_CONCEPT_CODE)));
+                        conceptNameFilter.add(criteriaBuilder.or(
                                 standardConceptPredicates.toArray(new Predicate[0])));
+
+                        predicates.add(
+                                criteriaBuilder.or(
+                                        criteriaBuilder.or(conceptCodeIDName.toArray(new Predicate[0])),
+                                        criteriaBuilder.and(conceptNameFilter.toArray(new Predicate[0]))
+                                ));
+                    } else {
+                        conceptCodeIDName.add(criteriaBuilder.greaterThan(matchExp, 0.0));
+                        predicates.add(
+                                criteriaBuilder.or(
+                                        conceptCodeIDName.toArray(new Predicate[0])
+                                )
+                        );
                     }
+
+
                     if (vocabularyIds != null) {
                         predicates.add(root.get("vocabularyId").in(vocabularyIds));
                     }
                     if (domainIds != null) {
                         predicates.add(root.get("domainId").in(domainIds));
                     }
+
                     return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
                 };
-
         // Return up to limit results, sorted in descending count value order.
         Pageable pageable = new PageRequest(0, limit,
                 new Sort(Direction.DESC, "countValue"));

diff --git a/common-api/src/main/java/org/pmiops/workbench/cdr/model/ConceptRelationship.java b/common-api/src/main/java/org/pmiops/workbench/cdr/model/ConceptRelationship.java
@@ -0,0 +1,51 @@
+package org.pmiops.workbench.cdr.model;
+
+import org.apache.commons.lang3.builder.ToStringBuilder;
+
+import javax.persistence.Column;
+import javax.persistence.Entity;
+import javax.persistence.EmbeddedId;
+import javax.persistence.Table;
+import javax.persistence.AttributeOverride;
+import javax.persistence.AttributeOverrides;
+
+import org.pmiops.workbench.cdr.model.ConceptRelationshipId;
+
+
+@Entity
+//TODO need to add a way to dynamically switch between database versions
+//this dynamic connection will eliminate the need for the catalog attribute
+@Table(name = "concept_relationship")
+public class ConceptRelationship {
+
+    private ConceptRelationshipId conceptRelationshipId;
+
+    @EmbeddedId
+    @AttributeOverrides({
+            @AttributeOverride(name="conceptId1",
+                    column=@Column(name="concept_id_1")),
+            @AttributeOverride(name="conceptId2",
+                    column=@Column(name="concept_id_2")),
+            @AttributeOverride(name="relationshipId",
+                    column=@Column(name="relationship_id"))
+    })
+    public ConceptRelationshipId getConceptRelationshipId() {
+        return conceptRelationshipId;
+    }
+
+    public void setConceptRelationshipId(ConceptRelationshipId conceptRelationshipId) {
+        this.conceptRelationshipId = conceptRelationshipId;
+    }
+
+    public ConceptRelationship conceptRelationshipId(ConceptRelationshipId conceptRelationshipId) {
+        this.conceptRelationshipId = conceptRelationshipId;
+        return this;
+    }
+
+    @Override
+    public String toString() {
+        return new ToStringBuilder(this)
+                .append("conceptRelationshipId", conceptRelationshipId)
+                .toString();
+    }
+}