/**
* 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);
}