/** * Recursive function used by ConceptSearchFactory::_buildSqlFromConceptSource * for each ConceptSearchTermCollection to create the criteria (the sql where clause). * If $apply_cs_filter is true, ConceptSearch filter will still only be applied if * filter_scope is TEXT and if a filter has been set in ConceptSearch. If false, the * filter will not be applied regardless of settings, which is the case for recursive * calls to _buildSqlCriteria. */ private function _buildSqlCriteria(ConceptSearch $cs, ConceptSearchTermCollection $cstc, ConceptSearchSource $css, ConceptSearchSource $css_sub_list_dict = null, $apply_cs_filter = true) { $arr_criteria = array(); // array of sql criteria to be glued together by cstc->glue $arr_csg_filter = array(); // explicit CSG level filters, e.g. "in" and "notin" operators // Set the source dictionary and get the database connection if ($css_sub_list_dict) { $css_dict = $css_sub_list_dict; } else { $css_dict = $css; } $conn = $css_dict->getConnection(); // ALL - get all concepts $is_all_search = false; // TODO: The 'is_all_search' variable is a complete hack used to apply global filters to // full list/mapsource queries. There needs to be a better way to split between // terms that should be filtered and those that should not. if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_ALL)) { $is_all_search = true; } // Child Collections if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_COLLECTION)) { foreach ($arr_search_term as $search_object) { $child_apply_cs_filter = false; if ($search_object->glue == MCL_CONCEPT_SEARCH_GLUE_AND) { $child_apply_cs_filter = true; } // TODO: The $child_apply_cs_filter logic is a HUGE hack and needs to be fixed $arr_criteria[] = $this->_buildSqlCriteria($cs, $search_object, $css, $css_sub_list_dict, $child_apply_cs_filter); } } // Concept ID - // Glue = OR : c.concept_id in (...) // Glue = AND : c.concept_id = ... and c.concept_id = ... if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_CONCEPT_ID)) { if ($cstc->glue == MCL_CONCEPT_SEARCH_GLUE_OR) { $arr_concept_id = array(); foreach ($arr_search_term as $search_object) { $arr_concept_id[] = $search_object->needle; } $arr_criteria[] = 'c.concept_id in (' . implode(',', $arr_concept_id) . ')'; } elseif ($cstc->glue == MCL_CONCEPT_SEARCH_GLUE_AND) { foreach ($arr_search_term as $search_object) { $arr_criteria[] = 'c.concept_id = ' . $search_object->needle; } } } // Concept ID Range if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_CONCEPT_ID_RANGE)) { foreach ($arr_search_term as $search_object) { list($min, $max) = $search_object->getRange(); $arr_criteria[] = 'c.concept_id >= ' . $min . ' and c.concept_id <= ' . $max; } } // Map Code - Numeric only // Glue = OR : c.concept_id in (select cm.concept_id from concept_map cm // where cm.source_code in (...)) // Glue = AND : c.concept_id in (select cm.concept_id from concept_map cm // where cm.source_code = ... ) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_MAP_CODE, true)) { if ($cstc->glue == MCL_CONCEPT_SEARCH_GLUE_OR) { $arr_map_code_numeric = array(); foreach ($arr_search_term as $search_object) { $arr_map_code_numeric[] = $search_object->needle; } $arr_criteria[] = 'c.concept_id in (select cm.concept_id from concept_map cm ' . 'where cm.source_code in (' . implode(',', $arr_map_code_numeric) . '))'; } elseif ($cstc->glue == MCL_CONCEPT_SEARCH_GLUE_AND) { foreach ($arr_search_term as $search_object) { $arr_criteria[] = 'c.concept_id in (select cm.concept_id from concept_map cm ' . 'where cm.source_code = ' . $search_object->needle . ')'; } } } // Map Code - Text only // Glue = OR : c.concept_id in (select cm.concept_id from concept_map cm // where cm.source_code in (...)) // Glue = AND : c.concept_id in (select cm.concept_id from concept_map cm // where cm.source_code = ... ) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_MAP_CODE, false)) { foreach ($arr_search_term as $search_term) { $arr_criteria[] = "c.concept_id in (select cm.concept_id from concept_map cm " . "where cm.source_code regexp '[[:<:]]" . mysql_real_escape_string($search_term->needle, $conn) . "')"; } } // Map Code Range if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_MAP_CODE_RANGE)) { list($min, $max) = $search_object->getRange(); $arr_map_code_numeric = array(); for ($i = $min; $i <= $max; $i++) { $arr_map_code_numeric[] = $i; } $arr_criteria[] = 'c.concept_id in (select cm.concept_id from concept_map cm ' . 'where cm.source_code in (' . implode(',', $arr_map_code_numeric) . '))'; } // Text - concept names AND descriptions (does not use concept_word) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_TEXT)) { // If FTS, combine terms and do a single boolean search if ($css_dict->dict_fulltext_mode == MCL_FULLTEXT_MODE_ON) { $_criteria = ''; foreach ($arr_search_term as $search_term) { if ($_criteria) { $_criteria .= ' '; } $_criteria .= '+' . mysql_real_escape_string($search_term->needle, $conn); } if ($_criteria) { $arr_criteria[] = "c.concept_id in (select cfts.concept_id from concept_fts cfts " . "where match(name, description, source_code) against('" . $_criteria . "' in boolean mode) )"; } } else { foreach ($arr_search_term as $search_term) { $arr_criteria[] = "(" . "c.concept_id in (select cn.concept_id from concept_name cn where cn.name regexp '[[:<:]]" . mysql_real_escape_string($search_term->needle, $conn) . "')" . ") OR (" . "c.concept_id in (select cd.concept_id from concept_description cd where cd.description regexp '[[:<:]]" . mysql_real_escape_string($search_term->needle, $conn) . "')" . ")"; } } } // Concept Name if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_CONCEPT_NAME)) { foreach ($arr_search_term as $search_term) { $arr_criteria[] = "c.concept_id in (select cn.concept_id from concept_name cn where cn.name regexp '[[:<:]]" . mysql_real_escape_string($search_term->needle, $conn) . "')"; } } // Concept Description if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_CONCEPT_DESCRIPTION)) { foreach ($arr_search_term as $search_term) { $arr_criteria[] = "c.concept_id in (select cdesc.concept_id from concept_description cdesc " . "where cdesc.description regexp '[[:<:]]" . mysql_real_escape_string($search_term->needle, $conn) . "')"; } } // UUID if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_UUID)) { if ($cstc->glue == MCL_CONCEPT_SEARCH_GLUE_OR) { $arr_uuid_full = array(); $arr_uuid_like = array(); foreach ($arr_search_term as $search_object) { $_needle = mysql_real_escape_string($search_object->needle, $conn); if (strlen($search_object->needle) < MCL_UUID_LENGTH) { $arr_criteria[] = "c.uuid LIKE '" . $_needle . "%'"; } else { $arr_uuid_full[] = "'" . $_needle . "'"; } if ($arr_uuid_full) { $arr_criteria[] = 'c.uuid in (' . implode(',', $arr_uuid_full) . ')'; } } } elseif ($cstc->glue == MCL_CONCEPT_SEARCH_GLUE_AND) { foreach ($arr_search_term as $search_object) { if (strlen($search_object->needle) < MCL_UUID_LENGTH) { $arr_criteria[] = "c.uuid LIKE '" . $_needle . "%'"; } else { $arr_criteria[] = "c.uuid = '" . $_needle . "'"; } } } } // IN inline filter (generically handles concept lists and map sources) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_IN)) { foreach ($arr_search_term as $search_term) { $css = $cs->getAllSources()->resolveSourceIdentifier($search_term->needle, $cs->getSelectedSources()->getDictionaries(), MCL_SOURCE_TYPE_LIST | MCL_SOURCE_TYPE_MAP); if (!$css) { continue; } elseif ($css->type == MCL_SOURCE_TYPE_LIST) { // Treat search_term as concept_list.concept_list_id $arr_csg_filter[] = "c.concept_id in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = " . $css->list_id . " and clm.dict_id = " . $css_dict->dict_id . ")"; } elseif ($css->type == MCL_SOURCE_TYPE_MAP) { // Treat search_term as concept_source.concept_source_id $arr_csg_filter[] = "c.concept_id in (" . "select cm.concept_id from concept_map cm " . "where cm.source = " . $css->map_source_id . ")"; } } } // NOT IN inline filter (generically handles concept lists and map sources) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_NOT_IN)) { foreach ($arr_search_term as $search_term) { $css = $cs->getAllSources()->resolveSourceIdentifier($search_term->needle, $cs->getSelectedSources()->getDictionaries(), MCL_SOURCE_TYPE_LIST | MCL_SOURCE_TYPE_MAP); if (!$css) { continue; } elseif ($css->type == MCL_SOURCE_TYPE_LIST) { // Treat search_term as concept_list.concept_list_id $arr_csg_filter[] = "c.concept_id not in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = " . $css->list_id . " and clm.dict_id = " . $css_dict->dict_id . ")"; } elseif ($css->type == MCL_SOURCE_TYPE_MAP) { // Treat search_term as concept_source.concept_source_id $arr_csg_filter[] = "c.concept_id not in (" . "select cm.concept_id from concept_map cm " . "where cm.source = " . $css->map_source_id . ")"; } } } // IN LIST inline filter (concept lists) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_IN_LIST)) { foreach ($arr_search_term as $search_term) { if ($search_term->isInteger()) { // Treat search_term as concept_list.concept_list_id $arr_csg_filter[] = "c.concept_id in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = " . $search_term->needle . " and clm.dict_id = " . $css_dict->dict_id . ")"; } else { // Treat search_term as concept_list.list_name $arr_csg_filter[] = "c.concept_id in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = (" . "select concept_list_id from mcl.concept_list " . "where lower(list_name) = '" . mysql_real_escape_string($search_term->needle, $conn) . "' " . ") " . "and clm.dict_id = " . $css_dict->dict_id . ")"; } } } // NOT IN LIST inline filter (concept lists) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_NOT_IN_LIST)) { foreach ($arr_search_term as $search_term) { if ($search_term->isInteger()) { // Treat search_term as concept_list.concept_list_id $arr_csg_filter[] = "c.concept_id not in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = " . $search_term->needle . " and clm.dict_id = " . $css_dict->dict_id . ")"; } else { // Treat search_term as concept_list.list_name $arr_csg_filter[] = "c.concept_id not in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = (" . "select concept_list_id from mcl.concept_list " . "where lower(list_name) = '" . mysql_real_escape_string($search_term->needle, $conn) . "' " . ") " . "and clm.dict_id = " . $css_dict->dict_id . ")"; } } } // IN SOURCE inline filter (map sources) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_IN_SOURCE)) { foreach ($arr_search_term as $search_term) { if ($search_term->isInteger()) { // Treat search_term as concept_source.concept_source_id $arr_csg_filter[] = "c.concept_id in (select cm.concept_id from concept_map cm where cm.source = " . $search_term->needle . ")"; } else { // Treat search_term as concept_source.name $arr_csg_filter[] = "c.concept_id in (select cm.concept_id from concept_map cm where cm.source = (" . "select cs.concept_source_id from concept_source cs where lower(name) = '" . mysql_real_escape_string($search_term->needle, $conn) . "'))"; } } } // NOT IN SOURCE inline filter (map sources) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_NOT_IN_SOURCE)) { foreach ($arr_search_term as $search_term) { if ($search_term->isInteger()) { // Treat search_term as concept_source.concept_source_id $arr_csg_filter[] = "c.concept_id not in (select cm.concept_id from concept_map cm where cm.source = " . $search_term->needle . ")"; } else { // Treat search_term as concept_source.name $arr_csg_filter[] = "c.concept_id not in (select cm.concept_id from concept_map cm where cm.source = (" . "select cs.concept_source_id from concept_source cs where lower(name) = '" . mysql_real_escape_string($search_term->needle, $conn) . "'))"; } } } // Concept List (only supported in enhanced mode, but that needs to be caught before it gets in here) if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_LIST)) { foreach ($arr_search_term as $search_term) { // Build the criteria $_sql_criteria = ''; if ($search_term->isInteger()) { // Treat search_term as concept_list.concept_list_id $_sql_criteria = "c.concept_id in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = " . $search_term->needle . " and clm.dict_id = " . $css_dict->dict_id . ")"; } else { // Treat search_term as concept_list.list_name $_sql_criteria = "c.concept_id in (" . "select clm.concept_id from mcl.concept_list_map clm " . "where clm.concept_list_id = (" . "select concept_list_id from mcl.concept_list " . "where lower(list_name) = '" . mysql_real_escape_string($search_term->needle, $conn) . "' " . ") " . "and clm.dict_id = " . $css_dict->dict_id . ")"; } /* Apply CS filter directly to this criteria, otherwise it will not be applied to * map sources and concept lists. */ if ($_sql_criteria && $cs->hasFilter()) { if ($_sql_filter = $this->_buildSqlFilter($cs, $css_dict)) { $_sql_criteria = '( ' . $_sql_criteria . ' ) AND ( ' . $_sql_filter . ' )'; } } // Add the criteria $arr_criteria[] = $_sql_criteria; } } // Map Source - pulls all concepts from a map source if ($arr_search_term = $cstc->getSearchTerms(MCL_SEARCH_TERM_TYPE_MAP_SOURCE)) { foreach ($arr_search_term as $search_term) { // Build the criteria $_sql_criteria = ''; if ($search_term->isInteger()) { // Treat search_term as concept_map.source $_sql_criteria = "c.concept_id in (" . "select cm.concept_id from concept_map cm " . "where cm.source = " . $search_term->needle . ")"; } else { // Treat search_term as concept_source.name $_sql_criteria = "c.concept_id in (" . "select cm.concept_id from concept_map cm " . "where cm.source = (" . "select cs.concept_source_id from concept_source cs " . "where lower(cs.name) = '" . mysql_real_escape_string($search_term->needle, $conn) . "' " . ") " . ")"; } /* Apply CS filter directly to this criteria, otherwise it will not be applied to * map sources and concept lists. */ if ($_sql_criteria && $cs->hasFilter()) { if ($_sql_filter = $this->_buildSqlFilter($cs, $css_dict)) { $_sql_criteria = '( ' . $_sql_criteria . ' ) AND ( ' . $_sql_filter . ' )'; } } // Add the criteria $arr_criteria[] = $_sql_criteria; } } // Combine all the where criteria (but not the filters) using the CSTC glue (OR or AND) $sql_where = ''; if ($arr_criteria) { $sql_where = '( ' . implode(' ) ' . $cstc->glue . ' ( ', $arr_criteria) . ' )'; } /* Apply CS filter if filter scope is text (i.e. this applies to the AND searches) * NOTE: Filter scope is typically text so that the filter does not apply to the OR searches, * such as hard coded IDs, mapcodes, etc. */ if (($is_all_search || $apply_cs_filter && $cs->filter_scope == MCL_FILTER_SCOPE_TEXT) && $cs->hasFilter()) { $sql_filter = $this->_buildSqlFilter($cs, $css_dict); if ($sql_filter) { if ($sql_where) { $sql_where = '( ' . $sql_where . ' ) AND ( ' . $sql_filter . ' )'; } else { $sql_where = '( ' . $sql_filter . ' )'; } } } // Apply CSG filter (IN and NOTIN operators) if ($arr_csg_filter) { $sql_csg_filter = '( ' . implode(' ) and ( ', $arr_csg_filter) . ' )'; if ($sql_where) { $sql_where = '( ' . $sql_where . ' ) and ' . $sql_csg_filter; } else { $sql_where = $sql_csg_filter; } } return $sql_where; }
/** * Parses the search group into ConceptSearchTermCollection * and ConceptSearchTerm objects. Returns true if the query contains * one or more search terms; false otherwise. */ public function parse($q) { $this->query = $q; if (!$q) { return false; } /* * Split into search terms. There are 5 types of terms currently supported: * text_or_integer * operator:text_or_integer * "text in, quotes with separators" * operator:"text in, quotes with separators" * Terms can be split by whitespace or commas. Quotes may be single or double. * * TODO: Support for functions or lists. e.g. fxn_name(fxn_param) */ $regexp = '~(?:(?:([\\w-]+):)?)(?:(?:"([^\\v"]*)")|(?:\'([^\\v\']*)\')|([\\w.-]+|\\*))(?=\\s|,|$)~'; $arr_matches = array(); $result = preg_match_all($regexp, $q, $arr_matches); // Create ConceptSearchTermCollection objects $this->cstc_top = new ConceptSearchTermCollection(); // top-level OR cstc $this->cstc_top->glue = MCL_CONCEPT_SEARCH_GLUE_OR; $cstc_and = new ConceptSearchTermCollection(); // child AND cstc for text searches $cstc_and->glue = MCL_CONCEPT_SEARCH_GLUE_AND; /** * Create search terms and add to appropriate CSTC. * Top-level CSTC has 'OR' glue and has one-child 'AND' CSTC for * text/name/desc search terms. */ $cstf = new ConceptSearchTermFactory(); $num_search_terms_created = 0; foreach ($arr_matches[0] as $str_search_term) { $arr_cst = $cstf->parse($str_search_term); foreach (array_keys($arr_cst) as $i) { $num_search_terms_created++; if ($arr_cst[$i]->term_type == MCL_SEARCH_TERM_TYPE_TEXT || $arr_cst[$i]->term_type == MCL_SEARCH_TERM_TYPE_CONCEPT_NAME || $arr_cst[$i]->term_type == MCL_SEARCH_TERM_TYPE_CONCEPT_DESCRIPTION) { $cstc_and->addSearchTerm($arr_cst[$i]); } else { $this->cstc_top->addSearchTerm($arr_cst[$i]); } } } if ($cstc_and->Count()) { $this->cstc_top->addSearchTermCollection($cstc_and); } return (bool) count($num_search_terms_created); }