private function _doQueriesForSqlSearch($po_rewritten_query, $pn_subject_tablenum, $ps_dest_table, $pn_level = 0)
{
// query is always of type Zend_Search_Lucene_Search_Query_Boolean
$vn_i = 0;
$va_old_signs = $po_rewritten_query->getSigns();
foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
$vb_is_blank_search = false;
if (is_null($va_old_signs)) {
// if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them
$vs_op = "AND";
} else {
if (is_null($va_old_signs[$vn_i])) {
// is the sign for a particular query is null then OR is (it is "neither required nor prohibited")
$vs_op = 'OR';
} else {
$vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND';
// true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation
}
}
if ($vn_i == 0) {
$vs_op = 'OR';
}
$va_direct_query_temp_tables = array();
// List of temporary tables created by direct search queries; tables listed here are dropped at the end of processing for the query element
switch (get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Boolean':
$this->_createTempTable('ca_sql_search_temp_' . $pn_level);
$this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, 'ca_sql_search_temp_' . $pn_level, $pn_level + 1);
// merge with current destination
switch ($vs_op) {
case 'AND':
// and
$this->_createTempTable($ps_dest_table . '_acc');
if ($vn_i == 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT DISTINCT row_id, boost\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT mfs.row_id, SUM(mfs.boost)\n\t\t\t\t\t\t\t\t\tFROM {$ps_dest_table} mfs\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_temp_{$pn_level} AS ftmp1 ON ftmp1.row_id = mfs.row_id\n\t\t\t\t\t\t\t\t\tGROUP BY mfs.row_id\n\t\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
$qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}");
$qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc");
}
$this->_dropTempTable($ps_dest_table . '_acc');
break;
case 'NOT':
$vs_sql = "\n\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} WHERE row_id IN\n\t\t\t\t\t\t\t\t(SELECT row_id FROM ca_sql_search_temp_{$pn_level})\n\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
break;
default:
case 'OR':
// or
$vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT row_id, SUM(boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\tGROUP BY row_id\n\t\t\t\t\t\t\t";
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql);
break;
}
$this->_dropTempTable('ca_sql_search_temp_' . $pn_level);
break;
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
case 'Zend_Search_Lucene_Search_Query_Phrase':
case 'Zend_Search_Lucene_Search_Query_Range':
$va_ft_terms = array();
$va_ft_like_terms = array();
$va_ft_stem_terms = array();
$vs_direct_sql_query = null;
$pa_direct_sql_query_params = null;
// set to array with values to use with direct SQL query placeholders or null to pass single standard table_num value as param (most queries just need this single value)
$va_tmp = array();
$vs_access_point = '';
$va_raw_terms = array();
switch (get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Range':
$va_lower_term = $o_lucene_query_element->getLowerTerm();
$va_upper_term = $o_lucene_query_element->getUpperTerm();
$va_element = $this->_getElementIDForAccessPoint($va_lower_term->field);
switch ($va_element['datatype']) {
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
$va_parsed_value = $t_geocode->parseValue($va_lower_term->text, $va_element['element_info']);
$vs_lower_lat = $va_parsed_value['value_decimal1'];
$vs_lower_long = $va_parsed_value['value_decimal2'];
$va_parsed_value = $t_geocode->parseValue($va_upper_term->text, $va_element['element_info']);
$vs_upper_lat = $va_parsed_value['value_decimal1'];
$vs_upper_long = $va_parsed_value['value_decimal2'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vs_lower_lat) . " AND " . floatval($vs_upper_lat) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($vs_lower_long) . " AND " . floatval($vs_upper_long) . ")\t\n\t\t\t\t\t\t\t\t\t";
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($va_lower_term->text, $va_element['element_info']);
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_cur->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_timecode->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_len->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_weight->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case 11:
// integer
$vn_lower_val = intval($va_lower_term->text);
$vn_upper_val = intval($va_upper_term->text);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case 12:
// decimal
$vn_lower_val = floatval($va_lower_term->text);
$vn_upper_val = floatval($va_upper_term->text);
break;
}
if (!$vs_direct_sql_query) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t";
}
break;
case 'Zend_Search_Lucene_Search_Query_Phrase':
$va_words = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_raw_terms[] = $vs_text = (string) $o_term->text;
if (strlen($vs_escaped_text = $this->opo_db->escape($vs_text))) {
$va_words[] = $vs_escaped_text;
}
}
if (!sizeof($va_words)) {
continue 3;
}
$va_ap_tmp = explode(".", $vs_access_point);
$vn_fld_table = $vn_fld_num = null;
if (sizeof($va_ap_tmp) == 2) {
$va_element = $this->_getElementIDForAccessPoint($vs_access_point);
if ($va_element) {
$vs_fld_num = $va_element['field_num'];
$vs_fld_table_num = $va_element['table_num'];
$vs_fld_limit_sql = " AND (swi.field_table_num = {$vs_fld_table_num} AND swi.field_num = '{$vs_fld_num}')";
}
}
$va_temp_tables = array();
$vn_w = 0;
foreach ($va_words as $vs_word) {
$vn_w++;
$vs_temp_table = 'ca_sql_search_phrase_' . md5($pn_subject_tablenum . "/" . $vs_word . "/" . $vn_w);
$this->_createTempTable($vs_temp_table);
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO {$vs_temp_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.index_id + 1, 1\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw \n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id \n\t\t\t\t\t\t\t\t\t" . (sizeof($va_temp_tables) ? " INNER JOIN " . $va_temp_tables[sizeof($va_temp_tables) - 1] . " AS tt ON swi.index_id = tt.row_id" : "") . "\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tsw.word = ? AND swi.table_num = ? {$vs_fld_limit_sql}\n \t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, $vs_word, (int) $pn_subject_tablenum);
$qr_count = $this->opo_db->query("SELECT count(*) c FROM {$vs_temp_table}");
if (!$qr_count->nextRow() || !(int) $qr_count->get('c')) {
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break 2;
}
$va_temp_tables[] = $vs_temp_table;
}
$vs_results_temp_table = array_pop($va_temp_tables);
$this->opo_db->query("UPDATE {$vs_results_temp_table} SET row_id = row_id - 1");
$va_direct_query_temp_tables[$vs_results_temp_table] = true;
$vs_direct_sql_query = "SELECT swi.row_id, ca.boost \n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM {$vs_results_temp_table} ca\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON swi.index_id = ca.row_id \n\t\t\t\t\t\t\t";
$pa_direct_sql_query_params = array();
// don't pass any params
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break;
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
$va_ft_like_term_list = array();
foreach ($o_lucene_query_element->getTerms() as $o_term) {
$va_raw_terms[] = $vs_term = (string) (method_exists($o_term, "getTerm") ? $o_term->getTerm()->text : $o_term->text);
if (!$vs_access_point && ($vs_field = method_exists($o_term, "getTerm") ? $o_term->getTerm()->field : $o_term->field)) {
$vs_access_point = $vs_field;
}
$vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term);
$va_ft_like_terms[] = $vs_stripped_term . ($vb_had_wildcard ? '%' : '');
}
break;
default:
$vs_access_point = $o_lucene_query_element->getTerm()->field;
$vs_term = $o_lucene_query_element->getTerm()->text;
if ($vs_access_point && mb_strtoupper($vs_term) == _t('[BLANK]')) {
$vb_is_blank_search = true;
break;
}
$va_terms = $this->_tokenize($vs_term, true, $vn_i);
$vb_output_term = false;
foreach ($va_terms as $vs_term) {
if (in_array(trim(mb_strtolower($vs_term, 'UTF-8')), WLPlugSearchEngineSqlSearch::$s_stop_words)) {
continue;
}
if (get_class($o_lucene_query_element) != 'Zend_Search_Lucene_Search_Query_MultiTerm') {
$vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term);
// do stemming
if ($this->opb_do_stemming) {
$vs_to_stem = preg_replace('!\\*$!u', '', $vs_term);
if (!preg_match('!y$!u', $vs_to_stem) && !preg_match('![0-9]+!', $vs_to_stem)) {
// don't stem things ending in 'y' as that can cause problems (eg "Bowery" becomes "Boweri")
if (!($vs_stem = trim($this->opo_stemmer->stem($vs_to_stem)))) {
$vs_stem = (string) $vs_term;
}
$va_ft_stem_terms[] = "'" . $this->opo_db->escape($vs_stem) . "'";
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
$vb_output_term = true;
}
}
if ($vb_output_term) {
$va_raw_terms[] = $vs_term;
} else {
$vn_i--;
}
break;
}
$vs_fld_num = $vs_table_num = $t_table = null;
$vb_ft_bit_optimization = false;
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
$o_tep = new TimeExpressionParser();
$vs_date = join(' ', $va_raw_terms);
if (!$o_tep->parse($vs_date)) {
break;
}
$va_range = $o_tep->getUnixTimestamps();
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
$vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : "";
switch ($vs_table) {
case 'created':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'I')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
case 'modified':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'U')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccls.subject_row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_change_log_subjects AS ccls ON ccls.log_id = ccl.log_id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccls.subject_table_num = {$pn_subject_tablenum})\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
}
} else {
if ($vs_table && $vs_field) {
$t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
if ($t_table) {
$vs_table_num = $t_table->tableNum();
if (is_numeric($vs_field)) {
$vs_fld_num = 'I' . $vs_field;
$vn_fld_num = (int) $vs_field;
} else {
$vn_fld_num = $this->getFieldNum($vs_table, $vs_field);
$vs_fld_num = 'I' . $vn_fld_num;
if (!strlen($vn_fld_num)) {
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
$vn_fld_num = $t_element->getPrimaryKey();
$vs_fld_num = 'A' . $vn_fld_num;
if (!$vb_is_blank_search) {
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case 2:
// dates
$vb_all_numbers = true;
foreach ($va_raw_terms as $vs_term) {
if (!is_numeric($vs_term)) {
$vb_all_numbers = false;
break;
}
}
$vs_raw_term = join(' ', $va_raw_terms);
$vb_exact = $vs_raw_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_raw_term, 1);
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
} else {
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 <= " . floatval($va_dates['start']) . " AND cav.value_decimal2 >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
}
break;
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
// If it looks like a lat/long pair that has been tokenized by Lucene
// into oblivion rehydrate it here.
if ($va_coords = caParseGISSearch(join(' ', $va_raw_terms))) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN {$va_coords['min_latitude']} AND {$va_coords['max_latitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN {$va_coords['min_longitude']} AND {$va_coords['max_longitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_amount = $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_amount) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue(array_shift($va_raw_terms), $t_element->getFieldValuesArray());
$vn_len = $va_parsed_value['value_decimal1'];
// this is always in meters so we can compare this value to the one in the database
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_len) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue(array_shift($va_raw_terms), $t_element->getFieldValuesArray());
$vn_weight = $va_parsed_value['value_decimal1'];
// this is always in kilograms so we can compare this value to the one in the database
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_weight) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_timecode = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_timecode) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 11:
// integer
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 = " . intval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case 12:
// decimal
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
}
}
} else {
// neither table fields nor elements, i.e. 'virtual' fields like _count should
$vn_fld_num = false;
$vs_fld_num = $vs_field;
}
}
}
if ($t_table->getFieldInfo($t_table->fieldName($vn_fld_num), 'FIELD_TYPE') == FT_BIT) {
$vb_ft_bit_optimization = true;
}
}
}
}
}
//
// If we're querying on the fulltext index then we need to construct
// the query here... if we already have a direct SQL query to run then we can skip this
//
if ($vb_is_blank_search) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (swi.word_id = 0))";
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} elseif (!$vs_direct_sql_query) {
$va_sql_where = array();
if (sizeof($va_ft_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.word IN (" . join(',', $va_ft_terms) . ")))";
} else {
if (sizeof($va_ft_terms) == 1) {
$va_sql_where[] = "(sw.word = " . $va_ft_terms[0] . ")";
} else {
$va_sql_where[] = "(sw.word IN (" . join(',', $va_ft_terms) . "))";
}
}
}
if (sizeof($va_ft_like_terms)) {
$va_tmp = array();
foreach ($va_ft_like_terms as $vs_term) {
if ($vb_ft_bit_optimization) {
$va_tmp[] = '(sw.word = \' ' . $this->opo_db->escape(trim($vs_term)) . ' \')';
} else {
$va_tmp[] = '(sw.word LIKE \'' . $this->opo_db->escape(trim($vs_term)) . '%\')';
}
}
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (" . join(' AND ', $va_tmp) . "))";
} else {
$va_sql_where[] = "(" . join(' AND ', $va_tmp) . ")";
}
}
if (sizeof($va_ft_stem_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.stem IN (" . join(',', $va_ft_stem_terms) . ")))";
} else {
$va_sql_where[] = "(sw.stem IN (" . join(',', $va_ft_stem_terms) . "))";
}
}
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} else {
$va_ft_terms = $va_ft_like_terms = $va_ft_like_terms = array();
}
//print "OP=$vs_op<br>";
if ($vn_i == 0) {
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "", $vs_direct_sql_query);
}
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t" . (!$vb_is_blank_search ? "INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id" : '') . "\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\tGROUP BY swi.row_id \n\t\t\t\t\t\t";
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1 && !$vs_direct_sql_query) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
if ($this->debug) {
print 'FIRST: ' . $vs_sql . " [{$pn_subject_tablenum}]<hr>\n";
}
//print $vs_sql;
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
} else {
switch ($vs_op) {
case 'AND':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = ca.row_id", $vs_direct_sql_query);
}
$this->_createTempTable($ps_dest_table . '_acc');
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table}_acc {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tINNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = swi.row_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t";
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
if ($this->debug) {
print 'AND:' . $vs_sql . "<hr>\n";
}
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
$qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}");
$qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc");
//$qr_res = $this->opo_db->query("TRUNCATE TABLE ca_sql_search_temp_2");
$this->_dropTempTable($ps_dest_table . '_acc');
break;
case 'NOT':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "", $vs_direct_sql_query);
}
$vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t" . ($vs_sql_where ? "{$vs_sql_where} AND " : "") . " swi.table_num = ? \n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '');
//print "$vs_sql<hr>";
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
$va_ids = $qr_res->getAllFieldValues("row_id");
$vs_sql = "\n\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} \n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\trow_id IN (?)\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
//print "$vs_sql<hr>";
break;
default:
case 'OR':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', "", $vs_direct_sql_query);
}
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t";
if ($this->debug) {
print 'OR' . $vs_sql . "<hr>\n";
}
$qr_res = $this->opo_db->query($vs_sql, is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum));
break;
}
}
// Drop any temporary tables created by direct search queries
foreach (array_keys($va_direct_query_temp_tables) as $vs_temp_table_to_drop) {
$this->_dropTempTable($vs_temp_table_to_drop);
}
break;
default:
//print get_class($o_lucene_query_element);
break;
}
$vn_i++;
}
}
public function search($pn_subject_tablenum, $ps_search_expression, $pa_filters = array(), $po_rewritten_query = null)
{
$va_solr_search_filters = array();
$vn_i = 0;
$va_old_signs = is_object($po_rewritten_query) ? $po_rewritten_query->getSigns() : array();
$va_terms = $va_signs = array();
if ($po_rewritten_query) {
foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
if (!$va_old_signs || !is_array($va_old_signs)) {
// if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them
$vs_op = "AND";
} else {
if (is_null($va_old_signs[$vn_i])) {
// is the sign for a particular query is null then OR is (it is "neither required nor prohibited")
$vs_op = 'OR';
} else {
$vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND';
// true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation
}
}
if ($vn_i == 0) {
$vs_op = 'OR';
}
// advanced search queries are for some reason nested 1-element boolean queries in boolean queries
if (get_class($o_lucene_query_element) == 'Zend_Search_Lucene_Search_Query_Boolean') {
$va_subqueries = $o_lucene_query_element->getSubqueries();
if (sizeof($va_subqueries) == 1) {
$o_lucene_query_element = array_shift($va_subqueries);
}
}
switch ($vs_class = get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
case 'Zend_Search_Lucene_Search_Query_Phrase':
$vs_access_point = '';
if ($vs_class != 'Zend_Search_Lucene_Search_Query_Term') {
$va_raw_terms = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_raw_terms[] = $vs_text = (string) $o_term->text;
}
$vs_term = join(" ", $va_raw_terms);
} else {
$vs_access_point = $o_lucene_query_element->getTerm()->field;
$vs_term = $o_lucene_query_element->getTerm()->text;
}
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
if (!$this->opo_tep->parse($vs_term)) {
break;
}
$va_range = $this->opo_tep->getUnixTimestamps();
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
$vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : "";
switch ($vs_table) {
case 'created':
if ($vn_user_id) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'created_user_id')), array(true, true));
} else {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'));
}
break;
case 'modified':
if ($vn_user_id) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'modified_user_id')), array(true, true));
} else {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'));
}
break;
}
} else {
if ($vs_table && $vs_field) {
$t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
if ($t_table) {
$vs_table_num = $t_table->tableNum();
if (is_numeric($vs_field)) {
$vs_fld_num = 'I' . $vs_field;
$vn_fld_num = (int) $vs_field;
} else {
$vn_fld_num = $this->getFieldNum($vs_table, $vs_field);
$vs_fld_num = 'I' . $vn_fld_num;
if (!strlen($vn_fld_num)) {
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
$vn_fld_num = $t_element->getPrimaryKey();
$vs_fld_num = 'A' . $vn_fld_num;
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case 2:
// dates
$vb_exact = $vs_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_term, 1);
if ($this->opo_tep->parse($vs_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
// TODO: fix date handling to reflect distinctions in ranges
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_table . '.' . $vs_fld_num));
}
} else {
if ($this->opo_tep->parse($vs_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
// TODO: fix date handling to reflect distinctions in ranges
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_table . '.' . $vs_fld_num));
}
}
break;
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
if ($va_coords = caParseGISSearch($vs_term)) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $va_coords['min_latitude'] . ',' . $va_coords['min_longitude'] . " TO " . $va_coords['max_latitude'] . ',' . $va_coords['max_longitude'] . ']', $vs_table . '.' . $vs_fld_num));
}
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_amount = (double) $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_amount, $vs_table . '.' . $vs_fld_num));
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_len = (double) $va_parsed_value['value_decimal1'];
// this is always in meters so we can compare this value to the one in the database
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_len, $vs_table . '.' . $vs_fld_num));
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_weight = (double) $va_parsed_value['value_decimal1'];
// this is always in kilograms so we can compare this value to the one in the database
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_weight, $vs_table . '.' . $vs_fld_num));
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_timecode = (double) $va_parsed_value['value_decimal1'];
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_timecode, $vs_table . '.' . $vs_fld_num));
break;
case 11:
// integer
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_table . '.' . $vs_fld_num));
break;
case 12:
// decimal
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_table . '.' . $vs_fld_num));
break;
default:
// everything else
$o_lucene_query_element->getTerm()->field = $vs_table . '.' . $vs_fld_num;
break;
}
} else {
$vn_fld_num = false;
$vs_fld_num = $vs_field;
}
}
}
}
}
}
}
break;
}
$va_terms[] = $o_lucene_query_element;
$va_signs[] = is_array($va_old_signs) ? array_key_exists($vn_i, $va_old_signs) ? $va_old_signs[$vn_i] : true : true;
$vn_i++;
}
$o_rewritten_query = new Zend_Search_Lucene_Search_Query_Boolean($va_terms, $va_signs);
$ps_search_expression = $this->_queryToString($o_rewritten_query);
}
if (is_array($pa_filters) && sizeof($pa_filters) && ($vs_filter_query = $this->_filterValueToQueryValue($pa_filters))) {
$ps_search_expression = "({$ps_search_expression}) AND ({$vs_filter_query})";
}
if (preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):(\"[^\"]*\")!", $ps_search_expression, $va_matches) || preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):([^ ]*)!", $ps_search_expression, $va_matches)) {
foreach ($va_matches[0] as $vn_i => $vs_element) {
$vs_fld = $va_matches[1][$vn_i];
if (!($vs_rel_type = trim($va_matches[2][$vn_i]))) {
continue;
}
$va_tmp = explode(".", $vs_fld);
$vs_rel_table = caGetRelationshipTableName($pn_subject_tablenum, $va_tmp[0]);
$va_rel_type_ids = $vs_rel_type && $vs_rel_table ? caMakeRelationshipTypeIDList($vs_rel_table, array($vs_rel_type)) : null;
$va_new_elements = array();
foreach ($va_rel_type_ids as $vn_rel_type_id) {
$va_new_elements[] = "(" . $va_matches[1][$vn_i] . "/" . $vn_rel_type_id . ":" . $va_matches[3][$vn_i] . ")";
}
$ps_search_expression = str_replace($vs_element, "(" . join(" OR ", $va_new_elements) . ")", $ps_search_expression);
}
}
$ps_search_expression = str_replace("/", '\\/', $ps_search_expression);
// escape forward slashes used to delimit relationship type qualifier
Debug::msg("[SOLR] Running query {$ps_search_expression}");
try {
$vo_http_client = new Zend_Http_Client();
$vo_http_client->setUri($this->ops_search_solr_url . "/" . $this->opo_datamodel->getTableName($pn_subject_tablenum) . "/select");
$vo_http_client->setParameterGet($va_get = array('q' => utf8_decode($ps_search_expression), 'wt' => 'json', 'fl' => $this->opo_datamodel->getTablePrimaryKeyName($pn_subject_tablenum), 'start' => $this->getOption('start'), 'rows' => $this->getOption('limit')));
$vo_http_response = $vo_http_client->request();
$va_result = json_decode($vo_http_response->getBody(), true);
} catch (Exception $e) {
caLogEvent('ERR', _t('Could not connect to SOLR server: %1', $e->getMessage()), 'Solr->search()');
$va_result["response"]["docs"] = array();
}
return new WLPlugSearchEngineSolrResult($va_result["response"]["docs"], $pn_subject_tablenum);
}
private function _doQueriesForSqlSearch($po_rewritten_query, $pn_subject_tablenum, $ps_dest_table, $pn_level = 0, $pa_options = null)
{
// query is always of type Zend_Search_Lucene_Search_Query_Boolean
$vn_i = 0;
switch (get_class($po_rewritten_query)) {
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
$va_elements = $po_rewritten_query->getTerms();
break;
default:
$va_elements = $po_rewritten_query->getSubqueries();
break;
}
$o_base = new SearchBase();
$va_old_signs = $po_rewritten_query->getSigns();
foreach ($va_elements as $o_lucene_query_element) {
$vb_is_blank_search = false;
if (is_null($va_old_signs)) {
// if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them
$vs_op = "AND";
} else {
if (is_null($va_old_signs[$vn_i])) {
// is the sign for a particular query is null then OR is (it is "neither required nor prohibited")
$vs_op = 'OR';
} else {
$vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND';
// true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation
}
}
if ($vn_i == 0) {
$vs_op = 'OR';
}
$va_direct_query_temp_tables = array();
// List of temporary tables created by direct search queries; tables listed here are dropped at the end of processing for the query element
$pa_direct_sql_query_params = null;
// set to array with values to use with direct SQL query placeholders or null to pass single standard table_num value as param (most queries just need this single value)
$vs_direct_sql_query = null;
$vn_direct_sql_target_table_num = $pn_subject_tablenum;
switch ($vs_class = get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Boolean':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
$this->_createTempTable('ca_sql_search_temp_' . $pn_level);
if ($vs_op == 'AND' && $vn_i == 0) {
$this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, $ps_dest_table, $pn_level + 1);
} else {
$this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, 'ca_sql_search_temp_' . $pn_level, $pn_level + 1);
}
// merge with current destination
switch ($vs_op) {
case 'AND':
if ($vn_i > 0) {
$this->_createTempTable("{$ps_dest_table}_acc");
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT mfs.row_id, SUM(mfs.boost)\n\t\t\t\t\t\t\t\t\tFROM {$ps_dest_table} mfs\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_temp_{$pn_level} AS ftmp1 ON ftmp1.row_id = mfs.row_id\n\t\t\t\t\t\t\t\t\tGROUP BY mfs.row_id\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql);
$qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}");
$qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc");
$this->_dropTempTable("{$ps_dest_table}_acc");
}
break;
case 'NOT':
$qr_res = $this->opo_db->query("SELECT row_id FROM ca_sql_search_temp_{$pn_level}");
if (is_array($va_ids = $qr_res->getAllFieldValues()) && sizeof($va_ids)) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} WHERE row_id IN (?)\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
}
break;
default:
case 'OR':
$vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT row_id, SUM(boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\tGROUP BY row_id\n\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql);
break;
}
$vn_i++;
$this->_dropTempTable('ca_sql_search_temp_' . $pn_level);
break;
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Index_Term':
case 'Zend_Search_Lucene_Search_Query_Phrase':
case 'Zend_Search_Lucene_Search_Query_Range':
$va_ft_terms = array();
$va_ft_like_terms = array();
$va_ft_stem_terms = array();
$vs_access_point = '';
$va_raw_terms = array();
switch (get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Range':
$va_lower_term = $o_lucene_query_element->getLowerTerm();
$va_upper_term = $o_lucene_query_element->getUpperTerm();
$va_element = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $va_lower_term->field);
$vn_direct_sql_target_table_num = $va_element['table_num'];
$va_indexed_fields = $o_base->getFieldsToIndex($pn_subject_tablenum, $vn_direct_sql_target_table_num);
$vn_root_element_id = $va_element['element_info']['hier_element_id'];
if (!isset($va_indexed_fields['_ca_attribute_' . $va_element['element_id']]) && (!$vn_root_element_id || $vn_root_element_id && !isset($va_indexed_fields['_ca_attribute_' . $vn_root_element_id]))) {
break 2;
}
// skip if not indexed
switch ($va_element['datatype']) {
case __CA_ATTRIBUTE_VALUE_GEOCODE__:
$t_geocode = new GeocodeAttributeValue();
$va_parsed_value = $t_geocode->parseValue('[' . $va_lower_term->text . ']', $va_element['element_info']);
$vs_lower_lat = $va_parsed_value['value_decimal1'];
$vs_lower_long = $va_parsed_value['value_decimal2'];
$va_parsed_value = $t_geocode->parseValue('[' . $va_upper_term->text . ']', $va_element['element_info']);
$vs_upper_lat = $va_parsed_value['value_decimal1'];
$vs_upper_long = $va_parsed_value['value_decimal2'];
// mysql BETWEEN always wants the lower value first ... BETWEEN 5 AND 3 wouldn't match 4 ... So we swap the values if necessary
if ($vs_upper_lat < $vs_lower_lat) {
$tmp = $vs_upper_lat;
$vs_upper_lat = $vs_lower_lat;
$vs_lower_lat = $tmp;
}
if ($vs_upper_long < $vs_lower_long) {
$tmp = $vs_upper_long;
$vs_upper_long = $vs_lower_long;
$vs_lower_long = $tmp;
}
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vs_lower_lat) . " AND " . floatval($vs_upper_lat) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($vs_lower_long) . " AND " . floatval($vs_upper_long) . ")\t\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_CURRENCY__:
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($va_lower_term->text, $va_element['element_info']);
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_cur->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_TIMECODE__:
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_timecode->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case __CA_ATTRIBUTE_VALUE_LENGTH__:
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_len->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case __CA_ATTRIBUTE_VALUE_WEIGHT__:
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($va_lower_term->text, $va_element['element_info']);
$vn_lower_val = $va_parsed_value['value_decimal1'];
$va_parsed_value = $t_weight->parseValue($va_upper_term->text, $va_element['element_info']);
$vn_upper_val = $va_parsed_value['value_decimal1'];
break;
case __CA_ATTRIBUTE_VALUE_INTEGER__:
$vn_lower_val = intval($va_lower_term->text);
$vn_upper_val = intval($va_upper_term->text);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_NUMERIC__:
$vn_lower_val = floatval($va_lower_term->text);
$vn_upper_val = floatval($va_upper_term->text);
break;
}
if (!$vs_direct_sql_query) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t";
}
break;
case 'Zend_Search_Lucene_Search_Query_Phrase':
if ($this->getOption('strictPhraseSearching')) {
$vs_search_tokenizer_regex = $this->opo_search_config->get('search_tokenizer_regex');
$va_words = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_terms = preg_split("![{$vs_search_tokenizer_regex}]+!u", (string) $o_term->text);
$va_raw_terms[] = (string) $o_term->text;
foreach ($va_terms as $vs_term) {
if (strlen($vs_escaped_text = $this->opo_db->escape($vs_term))) {
$va_words[] = $vs_escaped_text;
}
}
}
if (!sizeof($va_words)) {
continue 3;
}
$va_ap_tmp = explode(".", $vs_access_point);
$vn_fld_table = $vn_fld_num = null;
if (sizeof($va_ap_tmp) >= 2) {
$va_element = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $vs_access_point);
if ($va_element) {
$vs_fld_num = $va_element['field_num'];
$vs_fld_table_num = $va_element['table_num'];
$vs_fld_limit_sql = " AND (swi.field_table_num = {$vs_fld_table_num} AND swi.field_num = '{$vs_fld_num}')";
if (is_array($va_element['relationship_type_ids']) && sizeof($va_element['relationship_type_ids'])) {
$vs_fld_limit_sql .= " AND (swi.rel_type_id IN (" . join(",", $va_element['relationship_type_ids']) . "))";
}
}
}
$va_temp_tables = array();
$vn_w = 0;
foreach ($va_words as $vs_word) {
$vn_w++;
$vs_temp_table = 'ca_sql_search_phrase_' . md5($pn_subject_tablenum . "/" . $vs_word . "/" . $vn_w);
$this->_createTempTable($vs_temp_table);
$vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO {$vs_temp_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.index_id + 1, 1\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw \n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id \n\t\t\t\t\t\t\t\t\t" . (sizeof($va_temp_tables) ? " INNER JOIN " . $va_temp_tables[sizeof($va_temp_tables) - 1] . " AS tt ON swi.index_id = tt.row_id" : "") . "\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tsw.word = ? AND swi.table_num = ? {$vs_fld_limit_sql}\n \t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t";
$qr_res = $this->opo_db->query($vs_sql, $vs_word, (int) $pn_subject_tablenum);
$qr_count = $this->opo_db->query("SELECT count(*) c FROM {$vs_temp_table}");
if (!$qr_count->nextRow() || !(int) $qr_count->get('c')) {
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break 2;
}
$va_temp_tables[] = $vs_temp_table;
}
$vs_results_temp_table = array_pop($va_temp_tables);
$this->opo_db->query("UPDATE {$vs_results_temp_table} SET row_id = row_id - 1");
$va_direct_query_temp_tables[$vs_results_temp_table] = true;
$vs_direct_sql_query = "SELECT swi.row_id, ca.boost \n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM {$vs_results_temp_table} ca\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON swi.index_id = ca.row_id \n\t\t\t\t\t\t\t";
$pa_direct_sql_query_params = array();
// don't pass any params
foreach ($va_temp_tables as $vs_temp_table) {
$this->_dropTempTable($vs_temp_table);
}
break;
}
default:
switch ($vs_class) {
case 'Zend_Search_Lucene_Search_Query_Phrase':
$va_term_objs = $o_lucene_query_element->getQueryTerms();
break;
case 'Zend_Search_Lucene_Index_Term':
$va_term_objs = array($o_lucene_query_element);
break;
default:
$va_term_objs = array($o_lucene_query_element->getTerm());
break;
}
foreach ($va_term_objs as $o_term) {
$va_access_point_info = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $o_term->field);
$vs_access_point = $va_access_point_info['access_point'];
$vs_term = $o_term->text;
if ($vs_access_point && mb_strtoupper($vs_term) == _t('[BLANK]')) {
$t_ap = $this->opo_datamodel->getInstanceByTableNum($va_access_point_info['table_num'], true);
if (is_a($t_ap, 'BaseLabel')) {
// labels have the literal text "[Blank]" indexed to "blank" to indicate blank-ness
$vb_is_blank_search = false;
$vs_term = _t('blank');
} else {
$vb_is_blank_search = true;
break;
}
}
$va_terms = array($vs_term);
//$this->_tokenize($vs_term, true, $vn_i);
$vb_has_wildcard = (bool) preg_match('!\\*$!', $vs_term);
$vb_output_term = false;
foreach ($va_terms as $vs_term) {
if ($vb_has_wildcard) {
$vs_term .= '*';
}
if (in_array(trim(mb_strtolower($vs_term, 'UTF-8')), WLPlugSearchEngineSqlSearch::$s_stop_words)) {
continue;
}
$vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term);
if ($vb_has_wildcard) {
$va_ft_like_terms[] = $vs_stripped_term;
} else {
// do stemming
$vb_do_stemming = $this->opb_do_stemming;
if (mb_substr($vs_term, -1) == '|') {
$vs_term = mb_substr($vs_term, 0, mb_strlen($vs_term) - 1);
$vb_do_stemming = false;
}
if ($vb_do_stemming) {
$vs_to_stem = preg_replace('!\\*$!u', '', $vs_term);
if (!preg_match('!y$!u', $vs_to_stem) && !preg_match('![0-9]+!', $vs_to_stem)) {
// don't stem things ending in 'y' as that can cause problems (eg "Bowery" becomes "Boweri")
if (!($vs_stem = trim($this->opo_stemmer->stem($vs_to_stem)))) {
$vs_stem = (string) $vs_term;
}
$va_ft_stem_terms[] = "'" . $this->opo_db->escape($vs_stem) . "'";
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
} else {
$va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"';
}
}
$vb_output_term = true;
}
if ($vb_output_term) {
$va_raw_terms[] = $vs_term;
}
}
break;
}
$vs_fld_num = $vs_table_num = $t_table = null;
$vb_ft_bit_optimization = false;
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
$o_tep = new TimeExpressionParser();
$vs_date = join(' ', $va_raw_terms);
if (!$o_tep->parse($vs_date)) {
break;
}
$va_range = $o_tep->getUnixTimestamps();
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
$vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : "";
switch ($vs_table) {
case 'created':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'I')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
case 'modified':
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'U')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccls.subject_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_change_log_subjects AS ccls ON ccls.log_id = ccl.log_id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccls.subject_table_num = {$pn_subject_tablenum})\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t";
break;
}
} else {
if ($vs_table && $vs_field && ($t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true))) {
$vs_table_num = $t_table->tableNum();
if (is_numeric($vs_field)) {
$vs_fld_num = 'I' . $vs_field;
$vn_fld_num = (int) $vs_field;
} else {
$vn_fld_num = $this->getFieldNum($vs_table, $vs_field);
$vs_fld_num = 'I' . $vn_fld_num;
$vn_direct_sql_target_table_num = $vs_table_num;
if (!strlen($vn_fld_num)) {
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
$va_indexed_fields = $o_base->getFieldsToIndex($pn_subject_tablenum, $vn_direct_sql_target_table_num);
$vn_fld_num = $t_element->getPrimaryKey();
$vn_root_element_id = $t_element->get('hier_element_id');
if (!isset($va_indexed_fields['_ca_attribute_' . $vn_fld_num]) && (!$vn_root_element_id || $vn_root_element_id && !isset($va_indexed_fields['_ca_attribute_' . $vn_root_element_id]))) {
break 2;
}
// skip if not indexed
$vs_fld_num = 'A' . $vn_fld_num;
if (!$vb_is_blank_search) {
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case __CA_ATTRIBUTE_VALUE_DATERANGE__:
$vb_all_numbers = true;
foreach ($va_raw_terms as $vs_term) {
if (!is_numeric($vs_term)) {
$vb_all_numbers = false;
break;
}
}
$vs_raw_term = join(' ', $va_raw_terms);
$vb_exact = $vs_raw_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_raw_term, 1);
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
} else {
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 <= " . floatval($va_dates['start']) . " AND cav.value_decimal2 >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
}
break;
case __CA_ATTRIBUTE_VALUE_GEOCODE__:
// At this point $va_raw_terms has been tokenized by Lucene into oblivion
// and is also dependent on the search_tokenizer_regex so we can't really do anything with it.
// We now build our own un-tokenized term array instead. caParseGISSearch() can handle it.
$va_gis_terms = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
$va_gis_terms[] = trim((string) $o_term->text);
}
if ($va_coords = caParseGISSearch(join(' ', $va_gis_terms))) {
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN {$va_coords['min_latitude']} AND {$va_coords['max_latitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN {$va_coords['min_longitude']} AND {$va_coords['max_longitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
}
break;
case __CA_ATTRIBUTE_VALUE_CURRENCY__:
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_amount = $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_amount) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_LENGTH__:
// If it looks like a dimension that has been tokenized by Lucene
// into oblivion rehydrate it here.
try {
switch (sizeof($va_raw_terms)) {
case 2:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1];
break;
case 3:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1] . " " . $va_raw_terms[2];
break;
default:
$vs_dimension = join(' ', $va_raw_terms);
}
$vo_parsed_measurement = caParseLengthDimension($vs_dimension);
$vn_len = $vo_parsed_measurement->convertTo('METER', 6, 'en_US');
} catch (Exception $e) {
$vs_direct_sql_query = null;
break;
}
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_len) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_WEIGHT__:
// If it looks like a weight that has been tokenized by Lucene
// into oblivion rehydrate it here.
try {
switch (sizeof($va_raw_terms)) {
case 2:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1];
break;
case 3:
$vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1] . " " . $va_raw_terms[2];
break;
default:
$vs_dimension = join(' ', $va_raw_terms);
}
$vo_parsed_measurement = caParseWeightDimension($vs_dimension);
$vn_weight = $vo_parsed_measurement->convertTo('KILOGRAM', 6, 'en_US');
} catch (Exception $e) {
$vs_direct_sql_query = null;
break;
}
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_weight) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_TIMECODE__:
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray());
$vn_timecode = $va_parsed_value['value_decimal1'];
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_timecode) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_INTEGER__:
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 = " . intval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
case __CA_ATTRIBUTE_VALUE_NUMERIC__:
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t";
break;
}
}
} else {
// neither table fields nor elements, i.e. 'virtual' fields like _count should
$vn_fld_num = false;
$vs_fld_num = $vs_field;
}
}
}
if (($vs_intrinsic_field_name = $t_table->fieldName($vn_fld_num)) && ($vn_intrinsic_type = $t_table->getFieldInfo($vs_intrinsic_field_name, 'FIELD_TYPE')) == FT_BIT) {
$vb_ft_bit_optimization = true;
} elseif ($vn_intrinsic_type == FT_HISTORIC_DATERANGE) {
$vb_all_numbers = true;
foreach ($va_raw_terms as $vs_term) {
if (!is_numeric($vs_term)) {
$vb_all_numbers = false;
break;
}
}
$vs_date_start_fld = $t_table->getFieldInfo($vs_intrinsic_field_name, 'START');
$vs_date_end_fld = $t_table->getFieldInfo($vs_intrinsic_field_name, 'END');
$vs_raw_term = join(' ', $va_raw_terms);
$vb_exact = $vs_raw_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_raw_term, 1);
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $t_table->primaryKey() . ", 1\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $t_table->tableName() . "\n\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_end_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t";
}
} else {
if ($this->opo_tep->parse($vs_raw_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
$vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $t_table->primaryKey() . ", 1\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $t_table->tableName() . "\n\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_end_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} <= " . floatval($va_dates['start']) . " AND {$vs_date_end_fld} >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t";
}
}
$pa_direct_sql_query_params = array();
}
}
}
}
//
// If we're querying on the fulltext index then we need to construct
// the query here... if we already have a direct SQL query to run then we can skip this
//
if ($vb_is_blank_search) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (swi.word_id = 0))";
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} elseif (!$vs_direct_sql_query) {
$va_sql_where = array();
if (sizeof($va_ft_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.word IN (" . join(',', $va_ft_terms) . ")))";
} else {
if (sizeof($va_ft_terms) == 1) {
$va_sql_where[] = "(sw.word = " . $va_ft_terms[0] . ")";
} else {
$va_sql_where[] = "(sw.word IN (" . join(',', $va_ft_terms) . "))";
}
}
}
if (sizeof($va_ft_like_terms)) {
$va_tmp = array();
foreach ($va_ft_like_terms as $vs_term) {
if ($vb_ft_bit_optimization) {
$va_tmp[] = '(sw.word = \' ' . $this->opo_db->escape(trim($vs_term)) . ' \')';
} else {
$va_tmp[] = '(sw.word LIKE \'' . $this->opo_db->escape(trim($vs_term)) . '%\')';
}
}
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (" . join(' AND ', $va_tmp) . "))";
} else {
$va_sql_where[] = "(" . join(' AND ', $va_tmp) . ")";
}
}
if (sizeof($va_ft_stem_terms)) {
if ($t_table && strlen($vs_fld_num) > 1) {
$va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.stem IN (" . join(',', $va_ft_stem_terms) . ")))";
} else {
$va_sql_where[] = "(sw.stem IN (" . join(',', $va_ft_stem_terms) . "))";
}
}
if (!sizeof($va_sql_where)) {
continue;
}
$vs_sql_where = join(' OR ', $va_sql_where);
} else {
$va_ft_terms = $va_ft_like_terms = $va_ft_like_terms = array();
}
$vs_rel_type_id_sql = null;
if (is_array($va_access_point_info['relationship_type_ids']) && sizeof($va_access_point_info['relationship_type_ids'])) {
$vs_rel_type_id_sql = " AND (swi.rel_type_id IN (" . join(",", $va_access_point_info['relationship_type_ids']) . "))";
}
if (!$vs_fld_num && is_array($va_restrict_to_fields = caGetOption('restrictSearchToFields', $pa_options, null)) && sizeof($va_restrict_to_fields)) {
$va_field_restrict_sql = array();
foreach ($va_restrict_to_fields as $va_restrict) {
$va_field_restrict_sql[] = "((swi.field_table_num = " . intval($va_restrict['table_num']) . ") AND (swi.field_num = '" . $va_restrict['field_num'] . "'))";
}
$vs_sql_where .= " AND (" . join(" OR ", $va_field_restrict_sql) . ")";
}
$va_join = array();
if ($vn_direct_sql_target_table_num != $pn_subject_tablenum) {
// We're doing direct queries on metadata in a related table, fun!
// Now let's rewrite the direct query to work...
if ($t_target = $this->opo_datamodel->getInstanceByTableNum($vn_direct_sql_target_table_num, true)) {
// First we create the join from the related table to our subject
$vs_target_table_name = $t_target->tableName();
$va_path = array_keys($this->opo_datamodel->getPath($vn_direct_sql_target_table_num, $pn_subject_tablenum));
$vs_left_table = array_shift($va_path);
$vn_cj = 0;
foreach ($va_path as $vs_right_table) {
if (sizeof($va_rels = $this->opo_datamodel->getRelationships($vs_left_table, $vs_right_table)) > 0) {
$va_join[] = "INNER JOIN {$vs_right_table} ON {$vs_right_table}." . $va_rels[$vs_left_table][$vs_right_table][0][1] . " = " . ($vn_cj == 0 ? 'ca.row_id' : "{$vs_left_table}." . $va_rels[$vs_left_table][$vs_right_table][0][0]);
}
$vs_left_table = $vs_right_table;
$vn_cj++;
}
// Next we rewrite the key we're pulling to be from our subject
$vs_direct_sql_query = str_replace("SELECT ca.row_id", "SELECT " . $this->opo_datamodel->primaryKey($pn_subject_tablenum, true), $vs_direct_sql_query);
// Finally we pray
}
}
if ($vn_i == 0) {
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$vs_sql = "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}";
if (strpos($vs_sql, '?') !== false && (!is_array($pa_direct_sql_query_params) || sizeof($pa_direct_sql_query_params) == 0)) {
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
} else {
$vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t" . (!$vb_is_blank_search ? "INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id" : '') . "\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\tGROUP BY swi.row_id\n\t\t\t\t\t\t\t";
$pa_direct_sql_query_params = array((int) $pn_subject_tablenum);
}
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1 && !$vs_direct_sql_query) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
$t = new Timer();
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array();
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
$vn_i++;
if ($this->debug) {
Debug::msg('FIRST: ' . $vs_sql . " [{$pn_subject_tablenum}] " . $t->GetTime(4));
}
} else {
switch ($vs_op) {
case 'AND':
if ($vs_direct_sql_query) {
if ($vn_direct_sql_target_table_num != $pn_subject_tablenum) {
array_push($va_join, "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = " . $this->opo_datamodel->primaryKey($pn_subject_tablenum, true));
} else {
array_unshift($va_join, "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = ca.row_id");
}
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
$vs_sql = $vs_direct_sql_query ? "{$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY swi.row_id\n\t\t\t\t\t\t\t\t";
if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1) {
$vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}";
}
$t = new Timer();
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum);
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
if ($this->debug) {
Debug::msg('AND: ' . $vs_sql . ' ' . $t->GetTime(4) . ' ' . $qr_res->numRows());
}
if (is_array($va_ids = $qr_res->getAllFieldValues($vs_direct_sql_query && $vn_direct_sql_target_table_num != $pn_subject_tablenum ? $this->opo_datamodel->primaryKey($pn_subject_tablenum) : 'row_id')) && sizeof($va_ids)) {
$vs_sql = "DELETE FROM {$ps_dest_table} WHERE row_id NOT IN (?)";
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
if ($this->debug) {
Debug::msg('AND DELETE: ' . $vs_sql . ' ' . $t->GetTime(4));
}
} else {
// we don't have any results left, ie. our AND query should yield an empty result
$this->opo_db->query("DELETE FROM {$ps_dest_table}");
}
$vn_i++;
break;
case 'NOT':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
$vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t" . ($vs_sql_where ? "{$vs_sql_where} AND " : "") . " swi.table_num = ? \n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '');
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum);
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
$va_ids = $qr_res->getAllFieldValues($vs_direct_sql_query && $vn_direct_sql_target_table_num != $pn_subject_tablenum ? $this->opo_datamodel->primaryKey($pn_subject_tablenum) : 'row_id');
if (sizeof($va_ids) > 0) {
$vs_sql = "\n\t\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\trow_id IN (?)\n\t\t\t\t\t\t\t\t\t";
if ($this->debug) {
Debug::msg('NOT ' . $vs_sql);
}
$qr_res = $this->opo_db->query($vs_sql, array($va_ids));
}
$vn_i++;
break;
default:
case 'OR':
if ($vs_direct_sql_query) {
$vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query);
$pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum);
}
$vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t";
if ($this->debug) {
Debug::msg('OR ' . $vs_sql);
}
$vn_i++;
$pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum);
if (strpos($vs_sql, '?') === false) {
$pa_direct_sql_query_params = array();
}
$qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params);
break;
}
}
// Drop any temporary tables created by direct search queries
foreach (array_keys($va_direct_query_temp_tables) as $vs_temp_table_to_drop) {
$this->_dropTempTable($vs_temp_table_to_drop);
}
break;
default:
//print get_class($o_lucene_query_element);
break;
}
}
}
public function search($pn_subject_tablenum, $ps_search_expression, $pa_filters = array(), $po_rewritten_query = null)
{
$vn_i = 0;
$va_old_signs = $po_rewritten_query->getSigns();
$va_terms = $va_signs = array();
foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
switch ($vs_class = get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
case 'Zend_Search_Lucene_Search_Query_Phrase':
$vs_access_point = null;
if ($vs_class != 'Zend_Search_Lucene_Search_Query_Term') {
$va_raw_terms = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_raw_terms[] = $vs_text = (string) $o_term->text;
}
$vs_term = join(" ", $va_raw_terms);
} else {
$vs_access_point = $o_lucene_query_element->getTerm()->field;
$vs_term = $o_lucene_query_element->getTerm()->text;
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('"' . $vs_term . '"', $vs_access_point));
}
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
if (!$this->opo_tep->parse($vs_term)) {
break;
}
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
switch ($vs_table) {
case 'created':
if ($vn_user_id) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'created_user_id')), array(true, true));
} else {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'));
}
break;
case 'modified':
if ($vn_user_id) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'modified_user_id')), array(true, true));
} else {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'));
}
break;
}
} else {
if ($vs_table && $vs_field) {
$t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
if (!$t_table) {
break;
}
$vs_fld_num = $this->opo_datamodel->getFieldNum($vs_table, $vs_field);
if (!$vs_fld_num) {
// probably attribute
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case 2:
// dates
$vb_exact = $vs_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
if ($this->opo_tep->parse($vs_term)) {
// TODO: fix date handling to reflect distinctions in ranges
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
}
} else {
if ($this->opo_tep->parse($vs_term)) {
// TODO: fix date handling to reflect distinctions in ranges
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
}
}
break;
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
if ($va_coords = caParseGISSearch($vs_term)) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $va_coords['min_latitude'] . ',' . $va_coords['min_longitude'] . " TO " . $va_coords['max_latitude'] . ',' . $va_coords['max_longitude'] . ']', $vs_access_point));
}
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_amount = (double) $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_amount, $vs_access_point));
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_len = (double) $va_parsed_value['value_decimal1'];
// this is always in meters so we can compare this value to the one in the database
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_len, $vs_access_point));
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_weight = (double) $va_parsed_value['value_decimal1'];
// this is always in kilograms so we can compare this value to the one in the database
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_weight, $vs_access_point));
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_timecode = (double) $va_parsed_value['value_decimal1'];
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_timecode, $vs_access_point));
break;
case 11:
// integer
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
break;
case 12:
// decimal
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
break;
}
}
}
}
}
}
break;
}
$va_terms[] = $o_lucene_query_element;
$va_signs[] = is_array($va_old_signs) ? array_key_exists($vn_i, $va_old_signs) ? $va_old_signs[$vn_i] : true : true;
$vn_i++;
}
$o_rewritten_query = new Zend_Search_Lucene_Search_Query_Boolean($va_terms, $va_signs);
$ps_search_expression = $this->_queryToString($o_rewritten_query);
if ($vs_filter_query = $this->_filterValueToQueryValue($pa_filters)) {
$ps_search_expression = "({$ps_search_expression}) AND ({$vs_filter_query})";
}
$vo_http_client = new Zend_Http_Client();
$vo_http_client->setUri($this->ops_elasticsearch_base_url . "/" . $this->ops_elasticsearch_index_name . "/" . $this->opo_datamodel->getTableName($pn_subject_tablenum) . "/" . "_search");
if (preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):(\"[^\"]*\")!", $ps_search_expression, $va_matches) || preg_match_all("!([A-Za-z0-9_\\-\\.]+)[/]{1}([A-Za-z0-9_\\-]+):([^ ]*)!", $ps_search_expression, $va_matches)) {
foreach ($va_matches[0] as $vn_i => $vs_element) {
$vs_fld = $va_matches[1][$vn_i];
if (!($vs_rel_type = trim($va_matches[2][$vn_i]))) {
continue;
}
$va_tmp = explode(".", $vs_fld);
$vs_rel_table = caGetRelationshipTableName($pn_subject_tablenum, $va_tmp[0]);
$va_rel_type_ids = $vs_rel_type && $vs_rel_table ? caMakeRelationshipTypeIDList($vs_rel_table, array($vs_rel_type)) : null;
$va_new_elements = array();
foreach ($va_rel_type_ids as $vn_rel_type_id) {
$va_new_elements[] = "(" . $va_matches[1][$vn_i] . "/" . $vn_rel_type_id . ":" . $va_matches[3][$vn_i] . ")";
}
$ps_search_expression = str_replace($vs_element, "(" . join(" OR ", $va_new_elements) . ")", $ps_search_expression);
}
}
$ps_search_expression = str_replace("/", '\\/', $ps_search_expression);
// escape forward slashes used to delimit relationship type qualifier
Debug::msg("[ElasticSearch] Running query {$ps_search_expression}");
$vo_http_client->setParameterGet(array('size' => intval($this->opa_options["limit"]), 'q' => $ps_search_expression, 'fields' => ''));
$vo_http_response = $vo_http_client->request();
$va_result = json_decode($vo_http_response->getBody(), true);
return new WLPlugSearchEngineElasticSearchResult($va_result["hits"]["hits"], $pn_subject_tablenum);
}
public function search($pn_subject_tablenum, $ps_search_expression, $pa_filters = array(), $po_rewritten_query = null)
{
$t = new Timer();
$va_solr_search_filters = array();
$vn_i = 0;
$va_old_signs = $po_rewritten_query->getSigns();
$va_terms = $va_signs = array();
foreach ($po_rewritten_query->getSubqueries() as $o_lucene_query_element) {
if (!$va_old_signs || !is_array($va_old_signs)) {
// if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them
$vs_op = "AND";
} else {
if (is_null($va_old_signs[$vn_i])) {
// is the sign for a particular query is null then OR is (it is "neither required nor prohibited")
$vs_op = 'OR';
} else {
$vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND';
// true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation
}
}
if ($vn_i == 0) {
$vs_op = 'OR';
}
switch ($vs_class = get_class($o_lucene_query_element)) {
case 'Zend_Search_Lucene_Search_Query_Term':
case 'Zend_Search_Lucene_Search_Query_MultiTerm':
case 'Zend_Search_Lucene_Search_Query_Phrase':
if ($vs_class != 'Zend_Search_Lucene_Search_Query_Term') {
$va_raw_terms = array();
foreach ($o_lucene_query_element->getQueryTerms() as $o_term) {
if (!$vs_access_point && ($vs_field = $o_term->field)) {
$vs_access_point = $vs_field;
}
$va_raw_terms[] = $vs_text = (string) $o_term->text;
}
$vs_term = join(" ", $va_raw_terms);
} else {
$vs_access_point = $o_lucene_query_element->getTerm()->field;
$vs_term = $o_lucene_query_element->getTerm()->text;
}
if ($vs_access_point) {
list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point);
if (in_array($vs_table, array('created', 'modified'))) {
if (!$this->opo_tep->parse($vs_term)) {
break;
}
$va_range = $this->opo_tep->getUnixTimestamps();
$vn_user_id = null;
if ($vs_field = trim($vs_field)) {
if (!is_int($vs_field)) {
$t_user = new ca_users();
if ($t_user->load(array("user_name" => $vs_field))) {
$vn_user_id = (int) $t_user->getPrimaryKey();
}
} else {
$vn_user_id = (int) $vs_field;
}
}
$vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : "";
switch ($vs_table) {
case 'created':
if ($vn_user_id) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'created_user_id')), array(true, true));
} else {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'created'));
}
break;
case 'modified':
if ($vn_user_id) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Boolean(array(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'), new Zend_Search_Lucene_Index_Term($vn_user_id, 'modified_user_id')), array(true, true));
} else {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', 'modified'));
}
break;
}
} else {
if ($vs_table && $vs_field) {
$t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true);
if ($t_table) {
$vs_table_num = $t_table->tableNum();
if (is_numeric($vs_field)) {
$vs_fld_num = $vs_field;
} else {
$vs_fld_num = $this->opo_datamodel->getFieldNum($vs_table, $vs_field);
if (!$vs_fld_num) {
$t_element = new ca_metadata_elements();
if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) {
$vs_fld_num = $t_element->getPrimaryKey();
//
// For certain types of attributes we can directly query the
// attributes in the database rather than using the full text index
// This allows us to do "intelligent" querying... for example on date ranges
// parsed from natural language input and for length dimensions using unit conversion
//
switch ($t_element->get('datatype')) {
case 2:
// dates
$vb_exact = $vs_term[0] == "#" ? true : false;
// dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term
if ($vb_exact) {
$vs_raw_term = substr($vs_term, 1);
if ($this->opo_tep->parse($vs_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
// TODO: fix date handling to reflect distinctions in ranges
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
}
} else {
if ($this->opo_tep->parse($vs_term)) {
$va_dates = $this->opo_tep->getHistoricTimestamps();
// TODO: fix date handling to reflect distinctions in ranges
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $this->opo_tep->getText(array('start_as_iso8601' => true)) . " TO " . $this->opo_tep->getText(array('end_as_iso8601' => true)) . ']', $vs_access_point));
}
}
break;
case 4:
// geocode
$t_geocode = new GeocodeAttributeValue();
if ($va_coords = caParseGISSearch($vs_term)) {
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term('[' . $va_coords['min_latitude'] . ',' . $va_coords['min_longitude'] . " TO " . $va_coords['max_latitude'] . ',' . $va_coords['max_longitude'] . ']', $vs_access_point));
}
break;
case 6:
// currency
$t_cur = new CurrencyAttributeValue();
$va_parsed_value = $t_cur->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_amount = (double) $va_parsed_value['value_decimal1'];
$vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']);
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_amount, $vs_access_point));
break;
case 8:
// length
$t_len = new LengthAttributeValue();
$va_parsed_value = $t_len->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_len = (double) $va_parsed_value['value_decimal1'];
// this is always in meters so we can compare this value to the one in the database
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_len, $vs_access_point));
break;
case 9:
// weight
$t_weight = new WeightAttributeValue();
$va_parsed_value = $t_weight->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_weight = (double) $va_parsed_value['value_decimal1'];
// this is always in kilograms so we can compare this value to the one in the database
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_weight, $vs_access_point));
break;
case 10:
// timecode
$t_timecode = new TimecodeAttributeValue();
$va_parsed_value = $t_timecode->parseValue($vs_term, $t_element->getFieldValuesArray());
$vn_timecode = (double) $va_parsed_value['value_decimal1'];
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term($vn_timecode, $vs_access_point));
break;
case 11:
// integer
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
break;
case 12:
// decimal
$o_lucene_query_element = new Zend_Search_Lucene_Search_Query_Term(new Zend_Search_Lucene_Index_Term((double) $vs_term, $vs_access_point));
break;
}
}
}
}
}
}
}
}
break;
}
$va_terms[] = $o_lucene_query_element;
$va_signs[] = is_array($va_old_signs) ? array_key_exists($vn_i, $va_old_signs) ? $va_old_signs[$vn_i] : true : true;
$vn_i++;
}
$o_rewritten_query = new Zend_Search_Lucene_Search_Query_Boolean($va_terms, $va_signs);
$ps_search_expression = $this->_queryToString($o_rewritten_query);
if ($vs_filter_query = $this->_filterValueToQueryValue($pa_filters)) {
$ps_search_expression .= ' AND (' . $vs_filter_query . ')';
}
$vo_http_client = new Zend_Http_Client();
$vo_http_client->setUri($this->opo_search_config->get('search_elasticsearch_base_url') . "/" . $this->opo_search_config->get('search_elasticsearch_index_name') . "/" . $this->opo_datamodel->getTableName($pn_subject_tablenum) . "/" . "_search");
$vo_http_client->setParameterGet(array('size' => intval($this->opa_options["limit"]), 'q' => $ps_search_expression));
$vo_http_response = $vo_http_client->request();
$va_result = json_decode($vo_http_response->getBody(), true);
return new WLPlugSearchEngineElasticSearchResult($va_result["hits"]["hits"], $pn_subject_tablenum);
}