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); }
/** * Perform the browse using currently applied criteria, calculating the result set and browse facets * required for subsequent browse refinement. You need to call execute() after setting up your browse * criteria and options to: * • Get the result set reflecting the current browse state * • Fetch browse facets that reflect the current browse state * * @param array $pa_options Options include: * checkAccess = array of access values to filter facets that have an 'access' field by * noCache = don't use cached browse results * showDeleted = if set to true, related items that have been deleted are returned. Default is false. * limitToModifiedOn = if set returned results will be limited to rows modified within the specified date range. The value should be a date/time expression parse-able by TimeExpressionParser * user_id = If set item level access control is performed relative to specified user_id, otherwise defaults to logged in user * * @return bool True on success, null if the browse could not be executed (Eg. no settings), false no error */ public function execute($pa_options = null) { global $AUTH_CURRENT_USER_ID; if (!is_array($this->opa_browse_settings)) { return null; } if (!is_array($pa_options)) { $pa_options = array(); } $vn_user_id = caGetOption('user_id', $pa_options, $AUTH_CURRENT_USER_ID, array('castTo' => 'int')); $vb_no_cache = caGetOption('noCache', $pa_options, caGetOption('no_cache', $pa_options, false, array('castTo' => 'bool')), array('castTo' => 'bool')); $va_params = $this->opo_ca_browse_cache->getParameters(); $vb_need_to_cache_facets = false; $vb_results_cached = false; $vb_need_to_save_in_cache = false; $vs_cache_key = $this->opo_ca_browse_cache->getCurrentCacheKey(); if ($this->opo_ca_browse_cache->load($vs_cache_key)) { $vn_created_on = $this->opo_ca_browse_cache->getParameter('created_on'); //$t_new_browse->get('created_on', array('getDirectDate' => true)); $va_criteria = $this->getCriteria(); if (!$vb_no_cache && intval(time() - $vn_created_on) < $this->opo_ca_browse_config->get('cache_timeout')) { $vb_results_cached = true; $this->opo_ca_browse_cache->setParameter('created_on', time() + $this->opo_ca_browse_config->get('cache_timeout')); $vb_need_to_save_in_cache = true; Debug::msg("Cache hit for {$vs_cache_key}"); } else { $va_criteria = $this->getCriteria(); //$this->opo_ca_browse_cache->remove(); //$this->opo_ca_browse_cache->setParameter('criteria', $va_criteria); $vb_need_to_save_in_cache = true; $vb_need_to_cache_facets = true; Debug::msg("Cache expire for {$vs_cache_key}"); } } else { $va_criteria = $this->getCriteria(); $vb_need_to_save_in_cache = true; Debug::msg("Cache miss for {$vs_cache_key}"); } if (!$vb_results_cached) { $this->opo_ca_browse_cache->setParameter('sort', null); $this->opo_ca_browse_cache->setParameter('created_on', time()); $this->opo_ca_browse_cache->setParameter('table_num', $this->opn_browse_table_num); $vb_need_to_cache_facets = true; } $this->opb_criteria_have_changed = false; $t_item = $this->opo_datamodel->getInstanceByTableName($this->ops_browse_table_name, true); $va_results = array(); if (is_array($va_criteria) && sizeof($va_criteria) > 0) { if (!$vb_results_cached) { $va_acc = array(); $vn_i = 0; foreach ($va_criteria as $vs_facet_name => $va_row_ids) { $vs_target_browse_table_name = $t_item->tableName(); $vs_target_browse_table_num = $t_item->tableNum(); $vs_target_browse_table_pk = $t_item->primaryKey(); $va_facet_info = $this->getInfoForFacet($vs_facet_name); $va_row_ids = array_keys($va_row_ids); $vs_relative_to_join = ''; switch ($va_facet_info['type']) { # ----------------------------------------------------- case 'has': $vs_rel_table_name = $va_facet_info['table']; $va_joins = array(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_joins = array_merge($va_joins, $va_relative_execute_sql_data['relative_joins']); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } if ($va_facet_info['element_code']) { $t_element = new ca_metadata_elements(); if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) { break; } $vs_element_code = $va_facet_info['element_code']; $vn_state = array_pop($va_row_ids); if ($vn_state == 0) { $va_wheres[] = $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " NOT IN (SELECT row_id FROM ca_attributes WHERE table_num = " . $t_item->tableNum() . " AND element_id = " . $t_element->getPrimaryKey() . ")"; } else { $va_joins[] = "INNER JOIN ca_attributes AS caa ON caa.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND caa.table_num = " . $t_item->tableNum(); $va_wheres[] = "caa.element_id = " . $t_element->getPrimaryKey(); } } else { if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) { $va_restrict_to_relationship_types = array(); } $va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']); if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) { $va_exclude_relationship_types = array(); } $va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']); $vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name); $vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num); switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) { case 3: $t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true); $vs_key = 'relation_id'; break; case 2: $t_item_rel = null; $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $vs_key = $t_rel_item->primaryKey(); break; default: // bad related table return null; break; } $vs_cur_table = array_shift($va_path); $vn_state = array_pop($va_row_ids); foreach ($va_path as $vs_join_table) { $va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table); $va_joins[] = ($vn_state ? 'INNER' : 'LEFT') . ' JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n"; $vs_cur_table = $vs_join_table; } $va_wheres = array(); if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel) && (bool) $vn_state) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))"; } if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel) && (bool) $vn_state) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))"; } if (!(bool) $vn_state) { // no option $va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL)"; if ($t_rel_item->hasField('deleted')) { $va_wheres[] = "((" . $t_rel_item->tableName() . ".deleted = 0) OR (" . $t_rel_item->tableName() . ".deleted IS NULL))"; } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) { $va_wheres[] = "((" . $t_rel_item->tableName() . ".access NOT IN (" . join(',', $pa_options['checkAccess']) . ")) OR ((" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL) AND (" . $t_rel_item->tableName() . ".access IS NULL)))"; } } else { // yes option $va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NOT NULL)"; if ($t_rel_item->hasField('deleted')) { $va_wheres[] = "(" . $t_rel_item->tableName() . ".deleted = 0)"; } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) { $va_wheres[] = "(" . $t_rel_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } } } if ($t_item->hasField('deleted')) { $va_wheres[] = "(" . $t_item->tableName() . ".deleted = 0)"; } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) { $va_wheres[] = "(" . $t_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } $vs_join_sql = join("\n", $va_joins); $vs_where_sql = ''; if (sizeof($va_wheres) > 0) { $vs_where_sql = ' WHERE ' . join(' AND ', $va_wheres); } if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}"; $qr_res = $this->opo_db->query($vs_sql); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; break; # ----------------------------------------------------- # ----------------------------------------------------- case 'label': if (!($t_label = $t_item->getLabelTableInstance())) { break; } $vs_label_item_pk = $vs_item_pk = $t_item->primaryKey(); $vs_label_table_name = $t_label->tableName(); $vs_label_pk = $t_label->primaryKey(); $vs_label_display_field = $t_item->getLabelDisplayField(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; $t_target = $this->opo_datamodel->getInstanceByTableName($va_facet_info['relative_to'], true); $t_target_label = $t_target->getLabelTableInstance(); $vs_item_pk = $t_target->primaryKey(); $vs_label_table_name = $t_target_label->tableName(); $vs_label_item_pk = $t_target_label->primaryKey(); $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $va_relative_to_join[] = "INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}"; } } else { $va_relative_to_join = array("INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}"); } $vs_relative_to_join = join("\n", $va_relative_to_join); $va_labels = $t_item->getPreferredDisplayLabelsForIDs($va_row_ids); foreach ($va_row_ids as $vn_row_id) { if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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{$vs_label_table_name}.{$vs_label_display_field} = ?"; //print "$vs_sql [".intval($this->opn_browse_table_num)."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, $va_labels[$vn_row_id]); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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{$vs_label_table_name}.{$vs_label_display_field} = ?"; $qr_res = $this->opo_db->query($vs_sql, $va_labels[$vn_row_id]); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'field': $vs_field_name = $va_facet_info['field']; $vs_table_name = $this->ops_browse_table_name; if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode(str_replace('/', '/', $vn_row_id)); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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({$vs_table_name}.{$vs_field_name} = ?)"; $qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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({$vs_table_name}.{$vs_field_name} = ?)"; $qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'attribute': $t_element = new ca_metadata_elements(); if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) { return array(); } $vn_datatype = $t_element->get('datatype'); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } // TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) Text or Number attribute // (do we support other types as well?) $vn_element_id = $t_element->getPrimaryKey(); $o_attr = Attribute::getValueInstance($vn_datatype); foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); $vn_row_id = str_replace('/', '/', $vn_row_id); $va_value = $o_attr->parseValue($vn_row_id, $t_element->getFieldValuesArray()); $va_attr_sql = array(); $va_attr_values = array(intval($vs_target_browse_table_num), $vn_element_id); if (is_array($va_value)) { foreach ($va_value as $vs_f => $vs_v) { if ($vn_datatype == __CA_ATTRIBUTE_VALUE_LIST__) { if ($vs_f != 'item_id') { continue; } // Include sub-items $t_list_item = new ca_list_items(); $va_item_ids = $t_list_item->getHierarchy((int) $vs_v, array('idsOnly' => true, 'includeSelf' => true)); $va_item_ids[] = (int) $vs_v; $va_attr_sql[] = "(ca_attribute_values.{$vs_f} IN (?))"; $va_attr_values[] = $va_item_ids; } else { $va_attr_sql[] = "(ca_attribute_values.{$vs_f} " . (is_null($vs_v) ? " IS " : " = ") . " ?)"; $va_attr_values[] = $vs_v; } } } if ($vs_attr_sql = join(" AND ", $va_attr_sql)) { $vs_attr_sql = " AND " . $vs_attr_sql; } if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\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(ca_attribute_values.element_id = ?) {$vs_attr_sql}"; $qr_res = $this->opo_db->query($vs_sql, $va_attr_values); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\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(ca_attribute_values.element_id = ?) {$vs_attr_sql}"; $qr_res = $this->opo_db->query($vs_sql, $va_attr_values); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'normalizedDates': $t_element = new ca_metadata_elements(); $vb_is_element = $vb_is_field = false; if (!($vb_is_element = $t_element->load(array('element_code' => $va_facet_info['element_code']))) && !($vb_is_field = $t_item->hasField($va_facet_info['element_code']) && $t_item->getFieldInfo($va_facet_info['element_code'], 'FIELD_TYPE') === FT_HISTORIC_DATERANGE)) { return array(); } // TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) DateRange attribute $vs_normalization = $va_facet_info['normalization']; $o_tep = new TimeExpressionParser(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } $vn_element_id = $vb_is_element ? $t_element->getPrimaryKey() : null; $vs_browse_start_fld = $vs_browse_start_fld = null; if ($vb_is_field) { $vs_browse_start_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'START'); $vs_browse_end_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'END'); } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); $va_dates = null; if ($vn_row_id !== 'null') { if (!$o_tep->parse($vn_row_id)) { continue; } // invalid date? $va_dates = $o_tep->getHistoricTimestamps(); } if ($vb_is_element) { if (is_null($va_dates)) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\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(ca_attribute_values.element_id = ?) \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(ca_attribute_values.value_decimal1 IS NULL)\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(ca_attribute_values.value_decimal2 IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\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(ca_attribute_values.element_id = ?) AND\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\t\t\t(\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\t\t(ca_attribute_values.value_decimal1 <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 >= ?)\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\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\n\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(ca_attribute_values.value_decimal2 BETWEEN ? AND ?)\n\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"; $qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']); } } else { // is intrinsic if (is_null($va_dates)) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\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({$this->ops_browse_table_name}.{$vs_browse_start_fld} IS NULL)\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({$this->ops_browse_table_name}.{$vs_browse_end_fld} IS NULL)\n\t\t\t\t\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\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(\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\t\t({$this->ops_browse_table_name}.{$vs_browse_start_fld} <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_end_fld} >= ?)\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\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$this->ops_browse_table_name}.{$vs_browse_start_fld} BETWEEN ? AND ?)\n\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({$this->ops_browse_table_name}.{$vs_browse_end_fld} BETWEEN ? AND ?)\n\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"; $qr_res = $this->opo_db->query($vs_sql, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']); } } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'normalizedLength': $t_element = new ca_metadata_elements(); $vb_is_element = $vb_is_field = false; if (!($vb_is_element = $t_element->load(array('element_code' => $va_facet_info['element_code']))) && !($vb_is_field = $t_item->hasField($va_facet_info['element_code']) && $t_item->getFieldInfo($va_facet_info['element_code'], 'FIELD_TYPE') === FT_HISTORIC_DATERANGE)) { return array(); } // TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) DateRange attribute $vs_normalization = $va_facet_info['normalization']; $o_tep = new TimeExpressionParser(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } $vn_element_id = $vb_is_element ? $t_element->getPrimaryKey() : null; $vs_browse_start_fld = $vs_browse_start_fld = null; if ($vb_is_field) { $vs_browse_start_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'START'); $vs_browse_end_fld = $t_item->getFieldInfo($va_facet_info['element_code'], 'END'); } if (!($vs_output_units = caGetLengthUnitType($vs_units = caGetOption('units', $va_facet_info, 'm')))) { $vs_output_units = Zend_Measure_Length::METER; } $vs_increment = caGetOption('increment', $va_facet_info, '1 m'); $vo_increment = caParseLengthDimension($vs_increment); $vn_increment_in_current_units = (double) $vo_increment->convertTo($vs_output_units, 6, 'en_US'); foreach ($va_row_ids as $vn_row_id) { $vn_start = urldecode($vn_row_id); // is start dimension // calculate end dimension $vn_end = $vn_start + $vn_increment_in_current_units; // convert to meters $vo_start = new Zend_Measure_Length($vn_start, $vs_output_units, 'en_US'); $vo_end = new Zend_Measure_Length($vn_end, $vs_output_units, 'en_US'); $vn_start_in_meters = (double) $vo_start->convertTo(Zend_Measure_Length::METER, 6, 'en_US'); $vn_end_in_meters = (double) $vo_end->convertTo(Zend_Measure_Length::METER, 6, 'en_US'); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_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(ca_attribute_values.element_id = ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\n\t\t\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $vn_start_in_meters, $vn_end_in_meters); $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'authority': $vs_rel_table_name = $va_facet_info['table']; if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) { $va_restrict_to_relationship_types = array(); } $va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']); if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) { $va_exclude_relationship_types = array(); } $va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']); $vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name); $vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) { case 3: $t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true); $vs_key = 'relation_id'; break; case 2: $t_item_rel = null; $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $vs_key = $t_rel_item->primaryKey(); break; default: // bad related table return null; break; } $vs_cur_table = array_shift($va_path); $va_joins = array(); foreach ($va_path as $vs_join_table) { $va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table); $va_joins[] = 'INNER JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n"; $vs_cur_table = $vs_join_table; } $vs_join_sql = join("\n", $va_joins); $va_wheres = array(); if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))"; } if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))"; } $vs_where_sql = ''; if (sizeof($va_wheres) > 0) { $vs_where_sql = ' AND ' . join(' AND ', $va_wheres); } if ((!isset($va_facet_info['dont_expand_hierarchically']) || !$va_facet_info['dont_expand_hierarchically']) && $t_rel_item->isHierarchical() && $t_rel_item->load((int) $vn_row_id)) { $vs_hier_left_fld = $t_rel_item->getProperty('HIERARCHY_LEFT_INDEX_FLD'); $vs_hier_right_fld = $t_rel_item->getProperty('HIERARCHY_RIGHT_INDEX_FLD'); $vs_get_item_sql = "{$vs_rel_table_name}.{$vs_hier_left_fld} >= " . $t_rel_item->get($vs_hier_left_fld) . " AND {$vs_rel_table_name}.{$vs_hier_right_fld} <= " . $t_rel_item->get($vs_hier_right_fld); if ($vn_hier_id_fld = $t_rel_item->getProperty('HIERARCHY_ID_FLD')) { $vs_get_item_sql .= " AND {$vs_rel_table_name}.{$vn_hier_id_fld} = " . (int) $t_rel_item->get($vn_hier_id_fld); } $vs_get_item_sql = "({$vs_get_item_sql})"; } else { $vs_get_item_sql = "({$vs_rel_table_name}.{$vs_rel_table_pk} = " . (int) $vn_row_id . ")"; } if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\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{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}"; $qr_res = $this->opo_db->query($vs_sql); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\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{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}"; $qr_res = $this->opo_db->query($vs_sql); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'location': foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); $va_row_tmp = explode(":", $vn_row_id); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\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({$this->ops_browse_table_name}.current_loc_class = ?)" . (sizeof($va_row_tmp) > 1 ? " AND ({$this->ops_browse_table_name}.current_loc_subclass = ?)" : "") . (sizeof($va_row_tmp) > 2 ? " AND ({$this->ops_browse_table_name}.current_loc_id = ?)" : ""); $qr_res = $this->opo_db->query($vs_sql, $va_row_tmp); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\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({$this->ops_browse_table_name}.current_loc_class = ?)" . (sizeof($va_row_tmp) > 1 ? " AND ({$this->ops_browse_table_name}.current_loc_subclass = ?)" : "") . (sizeof($va_row_tmp) > 2 ? " AND ({$this->ops_browse_table_name}.current_loc_id = ?)" : ""); $qr_res = $this->opo_db->query($vs_sql, $va_row_tmp); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'fieldList': $vs_field_name = $va_facet_info['field']; $vs_table_name = $this->ops_browse_table_name; if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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({$vs_table_name}.{$vs_field_name} = ?)"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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({$vs_table_name}.{$vs_field_name} = ?)"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'violations': $vs_field_name = $va_facet_info['field']; $vs_table_name = $this->ops_browse_table_name; if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rule_violations ON ca_metadata_dictionary_rule_violations.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND ca_metadata_dictionary_rule_violations.table_num = " . $t_item->tableNum() . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rules ON ca_metadata_dictionary_rules.rule_id = ca_metadata_dictionary_rule_violations.rule_id\n\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\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(ca_metadata_dictionary_rules.rule_code = ?)"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); } else { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rule_violations ON ca_metadata_dictionary_rule_violations.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND ca_metadata_dictionary_rule_violations.table_num = " . $t_item->tableNum() . "\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_metadata_dictionary_rules ON ca_metadata_dictionary_rules.rule_id = ca_metadata_dictionary_rule_violations.rule_id\n\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\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(ca_metadata_dictionary_rules.rule_code = ?)"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); } $va_acc[$vn_i] = $qr_res->getAllFieldValues($this->ops_browse_table_name . '.' . $t_item->primaryKey()); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'checkouts': $vs_field_name = $va_facet_info['field']; $vs_table_name = $this->ops_browse_table_name; if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } $vs_where = null; $vn_current_time = time(); foreach ($va_row_ids as $vn_row_id) { $vs_checkout_join_sql = "INNER JOIN ca_object_checkouts ON ca_object_checkouts.object_id = ca_objects.object_id"; $vs_status_code = isset($va_facet_info['status']) && $va_facet_info['status'] ? $va_facet_info['status'] : $vn_row_id; switch ($vs_status_code) { case 'overdue': $vs_where = "((ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL) AND (ca_object_checkouts.due_date <= {$vn_current_time}))"; break; case 'reserved': $vs_where = "((ca_object_checkouts.checkout_date IS NULL) AND (ca_object_checkouts.return_date IS NULL))"; break; case 'available': $vs_checkout_join_sql = ''; $vs_where = "(ca_objects.object_id NOT IN (SELECT object_id FROM ca_object_checkouts WHERE (ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL)))"; break; case 'all': $vs_where = "(ca_object_checkouts.checkout_date <= {$vn_current_time})"; break; default: case 'out': $vs_where = "((ca_object_checkouts.checkout_date <= {$vn_current_time}) AND (ca_object_checkouts.return_date IS NULL))"; break; } $vs_sql = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca_objects.object_id\n\t\t\t\t\t\t\t\t\t\tFROM ca_objects\n\t\t\t\t\t\t\t\t\t\t{$vs_checkout_join_sql}\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t{$vs_where}\n\t\t\t\t\t\t\t\t\t"; $vs_user_sql = null; $va_params = array(); switch ($va_facet_info['mode']) { case 'user': foreach ($va_row_ids as $vn_index => $vn_row_id) { $va_row_ids[$vn_index] = (int) $vn_row_id; } $va_params[] = $va_row_ids; $vs_user_sql .= " AND (ca_object_checkouts.user_id IN (?))"; break; case 'all': default: // noop break; } $qr_res = $this->opo_db->query($vs_sql . $vs_user_sql, $va_params); $va_acc[$vn_i] = $qr_res->getAllFieldValues('ca_objects.object_id'); $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- default: // handle "search" criteria - search engine queries that can be browsed if ($vs_facet_name === '_search') { if (!($o_search = caGetSearchInstance($this->ops_browse_table_name))) { $this->postError(2900, _t("Invalid search type"), "BrowseEngine->execute()"); break; } $vs_pk = $t_item->primaryKey(); if (is_array($va_type_ids = $this->getTypeRestrictionList()) && sizeof($va_type_ids)) { $o_search->setTypeRestrictions($va_type_ids); } if (is_array($va_source_ids = $this->getSourceRestrictionList()) && sizeof($va_source_ids)) { $o_search->setSourceRestrictions($va_source_ids); } $va_options = $pa_options; unset($va_options['sort']); // browse engine takes care of sort so there is no reason to waste time having the search engine do so $va_options['filterNonPrimaryRepresentations'] = true; // filter out non-primary representations in ca_objects results to save (a bit) of time $o_search->setOption('strictPhraseSearching', caGetOption('strictPhraseSearching', $va_options, true)); $qr_res = $o_search->search($va_row_ids[0], $va_options); if ($qr_res->numHits() > 0) { $va_acc[$vn_i] = $qr_res->getPrimaryKeyValues(); } $vn_i++; } else { $this->postError(2900, _t("Invalid criteria type"), "BrowseEngine->execute()"); } break; # ----------------------------------------------------- } } foreach ($va_acc as $vn_i => $va_hits) { $va_acc[$vn_i] = array_flip($va_hits); } $vn_smallest_list_index = null; foreach ($va_acc as $vn_i => $va_hits) { if (is_null($vn_smallest_list_index)) { $vn_smallest_list_index = $vn_i; continue; } if (sizeof($va_hits) < sizeof($va_acc[$vn_smallest_list_index])) { $vn_smallest_list_index = $vn_i; } } $va_res = array(); $va_acc_indices = array_keys($va_acc); if (is_array($va_acc[$vn_smallest_list_index])) { foreach ($va_acc[$vn_smallest_list_index] as $vn_row_id => $vb_dummy) { foreach ($va_acc_indices as $vn_i) { if ($vn_i == $vn_smallest_list_index) { continue; } if (!isset($va_acc[$vn_i][$vn_row_id])) { continue 2; } } $va_res[$vn_row_id] = true; } } if (sizeof($va_res)) { $vs_filter_join_sql = $vs_filter_where_sql = ''; $va_wheres = array(); $va_joins = array(); $vs_sql_distinct = ''; if (sizeof($this->opa_result_filters)) { $va_tmp = array(); foreach ($this->opa_result_filters as $va_filter) { $vm_val = $this->_filterValueToQueryValue($va_filter); $va_wheres[] = $this->ops_browse_table_name . '.' . $va_filter['field'] . " " . $va_filter['operator'] . " " . $vm_val; } } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) { $va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) { $va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)"; } if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) { $o_tep = new TimeExpressionParser(); if ($o_tep->parse($pa_options['limitToModifiedOn'])) { $va_range = $o_tep->getUnixTimestamps(); $va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . " AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum(); $va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id"; $va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))"; $vs_sql_distinct = 'DISTINCT'; // need to pull distinct rows since joining the change log can cause dupes } } if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) { $t_subject = $this->getSubjectInstance(); $va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . ')' . ($t_subject->getFieldInfo('type_id', 'IS_NULL') ? " OR (" . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . " IS NULL)" : '') . ')'; } if (is_array($va_browse_source_ids) && sizeof($va_browse_source_ids)) { $va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IN (' . join(', ', $va_browse_source_ids) . ') OR (' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IS NULL))'; } $vs_filter_where_sql = "WHERE (" . $this->ops_browse_table_name . "." . $t_item->primaryKey() . " IN (?)) "; if (sizeof($va_wheres)) { $vs_filter_where_sql .= ' AND (' . join(' AND ', $va_wheres) . ')'; } if (sizeof($va_joins)) { $vs_filter_join_sql = join("\n", $va_joins); } $qr_res = $this->opo_db->query("\n\t\t\t\t\t\t\tSELECT {$vs_sql_distinct} " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t\t", array(array_keys($va_res))); $va_results = $qr_res->getAllFieldValues($t_item->primaryKey()); if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) { $va_results = $this->filterHitsByACL($va_results, $this->opn_browse_table_num, $vn_user_id, __CA_ACL_READONLY_ACCESS__); } $this->opo_ca_browse_cache->setResults($va_results); $vb_need_to_save_in_cache = true; } else { // No results for some reason - we're here because we don't want to throw a SQL error $this->opo_ca_browse_cache->setResults($va_results = array()); $vb_need_to_save_in_cache = true; } } } else { // no criteria - don't try to find anything unless configured to do so $va_settings = $this->opo_ca_browse_config->getAssoc($this->ops_browse_table_name); if (isset($va_settings['show_all_for_no_criteria_browse']) && $va_settings['show_all_for_no_criteria_browse'] || isset($pa_options['showAllForNoCriteriaBrowse']) && $pa_options['showAllForNoCriteriaBrowse']) { $va_wheres = $va_joins = array(); $vs_pk = $t_item->primaryKey(); if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) { $va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) { $va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)"; } if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) { $o_tep = new TimeExpressionParser(); if ($o_tep->parse($pa_options['limitToModifiedOn'])) { $va_range = $o_tep->getUnixTimestamps(); $va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = " . $this->ops_browse_table_name . ".{$vs_pk} AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum(); $va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id"; $va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))"; $vs_sql_distinct = 'DISTINCT'; // need to pull distinct rows since joining the change log can cause dupes } } $va_browse_type_ids = $this->getTypeRestrictionList(); $va_browse_source_ids = $this->getSourceRestrictionList(); if (is_array($va_browse_type_ids) && sizeof($va_browse_type_ids) || is_array($va_browse_source_ids) && sizeof($va_browse_source_ids)) { $t_subject = $this->getSubjectInstance(); if (is_array($va_browse_type_ids) && sizeof($va_browse_type_ids)) { $va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . ')' . ($t_subject->getFieldInfo('type_id', 'IS_NULL') ? " OR (" . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . " IS NULL)" : '') . ')'; } if (is_array($va_browse_source_ids) && sizeof($va_browse_source_ids)) { $va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IN (' . join(', ', $va_browse_source_ids) . ') OR (' . $this->ops_browse_table_name . '.' . $t_subject->getSourceFieldName() . ' IS NULL))'; } } if (sizeof($va_wheres)) { $vs_filter_where_sql = 'WHERE ' . join(' AND ', $va_wheres); } if (sizeof($va_joins)) { $vs_filter_join_sql = join("\n", $va_joins); } $qr_res = $this->opo_db->query("\n\t\t\t\t\t\tSELECT {$vs_pk}\n\t\t\t\t\t\tFROM " . $t_item->tableName() . "\n\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t{$vs_pk}\n\t\t\t\t\t"); $va_results = $qr_res->getAllFieldValues($vs_pk); if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) { $va_results = array_keys($this->filterHitsByACL($va_results, $this->opn_browse_table_num, $vn_user_id, __CA_ACL_READONLY_ACCESS__)); } $this->opo_ca_browse_cache->setResults($va_results); } else { $this->opo_ca_browse_cache->setResults(array()); } $vb_need_to_save_in_cache = true; } if ($vb_need_to_cache_facets && !$pa_options['dontCheckFacetAvailability']) { $this->loadFacetContent($pa_options); } if ($vb_need_to_save_in_cache) { $this->opo_ca_browse_cache->save(); } return true; }
/** * Options: * rawDate - if true, returns date as an array of start and end historic timestames * sortable - if true a language-independent sortable representation is returned. * getDirectDate - get underlying historic timestamp (floatval) */ public function getDisplayValue($pa_options = null) { if (!is_array($pa_options)) { $pa_options = array(); } if (isset($pa_options['rawDate']) && $pa_options['rawDate']) { return array(0 => $this->opn_start_date, 1 => $this->opn_end_date, 'start' => $this->opn_start_date, 'end' => $this->opn_end_date); } if (isset($pa_options['GET_DIRECT_DATE']) && $pa_options['GET_DIRECT_DATE'] || isset($pa_options['getDirectDate']) && $pa_options['getDirectDate']) { return $this->opn_start_date; } if (isset($pa_options['sortable']) && $pa_options['sortable']) { if (!$this->opn_start_date || !$this->opn_end_date) { return null; } return $this->opn_start_date . '/' . $this->opn_end_date; } $o_config = Configuration::load(); $o_date_config = Configuration::load($o_config->get('datetime_config')); if ($o_date_config->get('dateFormat') == 'original') { return $this->ops_text_value; } else { $t_element = new ca_metadata_elements($this->getElementID()); $va_settings = $this->getSettingValuesFromElementArray($t_element->getFieldValuesArray(), array('isLifespan')); $o_tep = new TimeExpressionParser(); $o_tep->setHistoricTimestamps($this->opn_start_date, $this->opn_end_date); return $o_tep->getText(array_merge(array('isLifespan' => $va_settings['isLifespan']), $pa_options)); //$this->ops_text_value; } }
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); }
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) { $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); }
/** * Actually do the browse * * Options: * checkAccess = array of access values to filter facets that have an 'access' field by * no_cache = don't use cached browse results * showDeleted = if set to true, related items that have been deleted are returned. Default is false. * limitToModifiedOn = if set returned results will be limited to rows modified within the specified date range. The value should be a date/time expression parse-able by TimeExpressionParser * user_id = If set item level access control is performed relative to specified user_id, otherwise defaults to logged in user */ public function execute($pa_options = null) { global $AUTH_CURRENT_USER_ID; if (!is_array($pa_options)) { $pa_options = array(); } $vn_user_id = isset($pa_options['user_id']) && (int) $pa_options['user_id'] ? (int) $pa_options['user_id'] : (int) $AUTH_CURRENT_USER_ID; if (!is_array($this->opa_browse_settings)) { return null; } $va_params = $this->opo_ca_browse_cache->getParameters(); $vb_need_to_cache_facets = false; $vb_results_cached = false; $vb_need_to_save_in_cache = false; $vs_cache_key = $this->opo_ca_browse_cache->getCurrentCacheKey(); if ($this->opo_ca_browse_cache->load($vs_cache_key)) { $vn_created_on = $this->opo_ca_browse_cache->getParameter('created_on'); //$t_new_browse->get('created_on', array('GET_DIRECT_DATE' => true)); $va_criteria = $this->getCriteria(); if ((!isset($pa_options['no_cache']) || !$pa_options['no_cache']) && intval(time() - $vn_created_on) < $this->opo_ca_browse_config->get('cache_timeout')) { $vb_results_cached = true; //print "cache hit for [$vs_cache_key]<br>"; } else { $va_criteria = $this->getCriteria(); $this->opo_ca_browse_cache->remove(); $this->opo_ca_browse_cache->setParameter('criteria', $va_criteria); //print "cache expire for [$vs_cache_key]<br>"; $vb_need_to_save_in_cache = true; $vb_need_to_cache_facets = true; } } else { $va_criteria = $this->getCriteria(); //print "cache miss for [$vs_cache_key]<br>"; $vb_need_to_save_in_cache = true; } if (!$vb_results_cached) { $this->opo_ca_browse_cache->setParameter('sort', null); $this->opo_ca_browse_cache->setParameter('created_on', time()); $this->opo_ca_browse_cache->setParameter('table_num', $this->opn_browse_table_num); $vb_need_to_cache_facets = true; } $this->opb_criteria_have_changed = false; $t_item = $this->opo_datamodel->getInstanceByTableName($this->ops_browse_table_name, true); $va_results = array(); if (is_array($va_criteria) && sizeof($va_criteria) > 0) { if (!$vb_results_cached) { // generate results $this->_createTempTable('ca_browses_acc'); $this->_createTempTable('ca_browses_tmp'); $vn_i = 0; foreach ($va_criteria as $vs_facet_name => $va_row_ids) { $vs_target_browse_table_name = $t_item->tableName(); $vs_target_browse_table_num = $t_item->tableNum(); $vs_target_browse_table_pk = $t_item->primaryKey(); $va_facet_info = $this->getInfoForFacet($vs_facet_name); $va_row_ids = array_keys($va_row_ids); $vs_relative_to_join = ''; switch ($va_facet_info['type']) { # ----------------------------------------------------- case 'has': $vs_rel_table_name = $va_facet_info['table']; $va_joins = array(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_joins = array_merge($va_joins, $va_relative_execute_sql_data['relative_joins']); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } if ($va_facet_info['element_code']) { $t_element = new ca_metadata_elements(); if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) { break; } $vs_element_code = $va_facet_info['element_code']; $vn_state = array_pop($va_row_ids); if ($vn_state == 0) { $va_wheres[] = $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " NOT IN (SELECT row_id FROM ca_attributes WHERE table_num = " . $t_item->tableNum() . " AND element_id = " . $t_element->getPrimaryKey() . ")"; } else { $va_joins[] = "INNER JOIN ca_attributes AS caa ON caa.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . " AND caa.table_num = " . $t_item->tableNum(); $va_wheres[] = "caa.element_id = " . $t_element->getPrimaryKey(); } } else { if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) { $va_restrict_to_relationship_types = array(); } $va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']); if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) { $va_exclude_relationship_types = array(); } $va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']); $vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name); $vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num); switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) { case 3: $t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true); $vs_key = 'relation_id'; break; case 2: $t_item_rel = null; $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $vs_key = $t_rel_item->primaryKey(); break; default: // bad related table return null; break; } $vs_cur_table = array_shift($va_path); $vn_state = array_pop($va_row_ids); foreach ($va_path as $vs_join_table) { $va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table); $va_joins[] = ($vn_state ? 'INNER' : 'LEFT') . ' JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n"; $vs_cur_table = $vs_join_table; } $va_wheres = array(); if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))"; } if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))"; } if (!(bool) $vn_state) { // no option $va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL)"; if ($t_rel_item->hasField('deleted')) { $va_wheres[] = "((" . $t_rel_item->tableName() . ".deleted = 0) OR (" . $t_rel_item->tableName() . ".deleted IS NULL))"; } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) { $va_wheres[] = "((" . $t_rel_item->tableName() . ".access NOT IN (" . join(',', $pa_options['checkAccess']) . ")) OR ((" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NULL) AND (" . $t_rel_item->tableName() . ".access IS NULL)))"; } } else { // yes option $va_wheres[] = "(" . $t_rel_item->tableName() . "." . $t_rel_item->primaryKey() . " IS NOT NULL)"; if ($t_rel_item->hasField('deleted')) { $va_wheres[] = "(" . $t_rel_item->tableName() . ".deleted = 0)"; } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_rel_item->hasField('access')) { $va_wheres[] = "(" . $t_rel_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } } } if ($t_item->hasField('deleted')) { $va_wheres[] = "(" . $t_item->tableName() . ".deleted = 0)"; } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) { $va_wheres[] = "(" . $t_item->tableName() . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } $vs_join_sql = join("\n", $va_joins); $vs_where_sql = ''; if (sizeof($va_wheres) > 0) { $vs_where_sql = ' WHERE ' . join(' AND ', $va_wheres); } if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}\n\t\t\t\t\t\t\t\t\t\t\t"; //print "$vs_sql<hr>"; $qr_res = $this->opo_db->query($vs_sql); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}"; //print "$vs_sql<hr>"; $qr_res = $this->opo_db->query($vs_sql); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; break; # ----------------------------------------------------- # ----------------------------------------------------- case 'label': if (!($t_label = $t_item->getLabelTableInstance())) { break; } $vs_label_item_pk = $vs_item_pk = $t_item->primaryKey(); $vs_label_table_name = $t_label->tableName(); $vs_label_pk = $t_label->primaryKey(); $vs_label_display_field = $t_item->getLabelDisplayField(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; $t_target = $this->opo_datamodel->getInstanceByTableName($va_facet_info['relative_to'], true); $t_target_label = $t_target->getLabelTableInstance(); $vs_item_pk = $t_target->primaryKey(); $vs_label_table_name = $t_target_label->tableName(); $vs_label_item_pk = $t_target_label->primaryKey(); $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $va_relative_to_join[] = "INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}"; } } else { $va_relative_to_join = array("INNER JOIN {$vs_label_table_name} ON {$vs_label_table_name}.{$vs_label_item_pk} = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk}"); } $vs_relative_to_join = join("\n", $va_relative_to_join); foreach ($va_row_ids as $vn_row_id) { if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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{$vs_label_table_name}.{$vs_label_item_pk} = ?"; //print "$vs_sql [".intval($this->opn_browse_table_num)."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . "." . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\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{$vs_label_table_name}.{$vs_label_item_pk} = ?"; //print "$vs_sql [".intval($this->opn_browse_table_num)."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'field': $vs_field_name = $va_facet_info['field']; $vs_table_name = $this->ops_browse_table_name; if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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({$vs_table_name}.{$vs_field_name} = ?)"; //print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_element_id."/".$vn_row_id."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\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({$vs_table_name}.{$vs_field_name} = ?)"; //print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_element_id."/".$vn_row_id."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, (string) $vn_row_id); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'attribute': $t_element = new ca_metadata_elements(); if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) { return array(); } $vn_datatype = $t_element->get('datatype'); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } // TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) Text or Number attribute // (do we support other types as well?) $vn_element_id = $t_element->getPrimaryKey(); $o_attr = Attribute::getValueInstance($t_element->get('datatype')); foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); $vn_row_id = str_replace('/', '/', $vn_row_id); $va_value = $o_attr->parseValue($vn_row_id, $t_element->getFieldValuesArray()); $va_attr_sql = array(); $va_attr_values = array(intval($vs_target_browse_table_num), $vn_element_id); if (is_array($va_value)) { foreach ($va_value as $vs_f => $vs_v) { if ($vn_datatype == 3) { // list $t_list_item = new ca_list_items((int) $vs_v); // Include sub-items $va_item_ids = $t_list_item->getHierarchy((int) $vs_v, array('idsOnly' => true, 'includeSelf' => true)); $va_item_ids[] = (int) $vs_v; $va_attr_sql[] = "(ca_attribute_values.{$vs_f} IN (?))"; $va_attr_values[] = $va_item_ids; } else { $va_attr_sql[] = "(ca_attribute_values.{$vs_f} " . (is_null($vs_v) ? " IS " : " = ") . " ?)"; $va_attr_values[] = $vs_v; } } } if ($vs_attr_sql = join(" AND ", $va_attr_sql)) { $vs_attr_sql = " AND " . $vs_attr_sql; } if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\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(ca_attribute_values.element_id = ?) {$vs_attr_sql}"; //caDebug($vs_sql); //caDebug(intval($vs_target_browse_table_num)."/".$vn_element_id."/".$vn_row_id); //caDebug($va_attr_values); $qr_res = $this->opo_db->query($vs_sql, $va_attr_values); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = {$vs_target_browse_table_name}.{$vs_target_browse_table_pk} AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\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(ca_attribute_values.element_id = ?) {$vs_attr_sql}"; //print "$vs_sql [".intval($vs_target_browse_table_num)."/".$vn_element_id."/".$vn_row_id."]<hr>";print_R($va_attr_values); $qr_res = $this->opo_db->query($vs_sql, $va_attr_values); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'normalizedDates': $t_element = new ca_metadata_elements(); if (!$t_element->load(array('element_code' => $va_facet_info['element_code']))) { return array(); } // TODO: check that it is a *single-value* (ie. no hierarchical ca_metadata_elements) DateRange attribute $vs_normalization = $va_facet_info['normalization']; $vn_element_id = $t_element->getPrimaryKey(); $o_tep = new TimeExpressionParser(); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); if (!$o_tep->parse($vn_row_id)) { continue; } // invalid date? $va_dates = $o_tep->getHistoricTimestamps(); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\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(ca_attribute_values.element_id = ?) AND\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(\n\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(ca_attribute_values.value_decimal1 <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 >= ?)\n\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\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\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(ca_attribute_values.value_decimal2 BETWEEN ? AND ?)\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"; //print $vs_sql; $qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes ON ca_attributes.row_id = " . $vs_target_browse_table_name . '.' . $vs_target_browse_table_pk . " AND ca_attributes.table_num = ?\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attribute_values ON ca_attribute_values.attribute_id = ca_attributes.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $t_item->tableName() . '.' . $t_item->primaryKey() . "\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(ca_attribute_values.element_id = ?) AND\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(\n\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(ca_attribute_values.value_decimal1 <= ?) AND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal2 >= ?)\n\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\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t(ca_attribute_values.value_decimal1 BETWEEN ? AND ?)\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(ca_attribute_values.value_decimal2 BETWEEN ? AND ?)\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"; //print $vs_sql; $qr_res = $this->opo_db->query($vs_sql, intval($vs_target_browse_table_num), $vn_element_id, $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end'], $va_dates['start'], $va_dates['end']); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'authority': $vs_rel_table_name = $va_facet_info['table']; if (!is_array($va_restrict_to_relationship_types = $va_facet_info['restrict_to_relationship_types'])) { $va_restrict_to_relationship_types = array(); } $va_restrict_to_relationship_types = $this->_getRelationshipTypeIDs($va_restrict_to_relationship_types, $va_facet_info['relationship_table']); if (!is_array($va_exclude_relationship_types = $va_facet_info['exclude_relationship_types'])) { $va_exclude_relationship_types = array(); } $va_exclude_relationship_types = $this->_getRelationshipTypeIDs($va_exclude_relationship_types, $va_facet_info['relationship_table']); $vn_table_num = $this->opo_datamodel->getTableNum($vs_rel_table_name); $vs_rel_table_pk = $this->opo_datamodel->getTablePrimaryKeyName($vn_table_num); if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { switch (sizeof($va_path = array_keys($this->opo_datamodel->getPath($vs_target_browse_table_name, $vs_rel_table_name)))) { case 3: $t_item_rel = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[2], true); $vs_key = 'relation_id'; break; case 2: $t_item_rel = null; $t_rel_item = $this->opo_datamodel->getInstanceByTableName($va_path[1], true); $vs_key = $t_rel_item->primaryKey(); break; default: // bad related table return null; break; } $vs_cur_table = array_shift($va_path); $va_joins = array(); foreach ($va_path as $vs_join_table) { $va_rel_info = $this->opo_datamodel->getRelationships($vs_cur_table, $vs_join_table); $va_joins[] = 'INNER JOIN ' . $vs_join_table . ' ON ' . $vs_cur_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][0] . ' = ' . $vs_join_table . '.' . $va_rel_info[$vs_cur_table][$vs_join_table][0][1] . "\n"; $vs_cur_table = $vs_join_table; } $vs_join_sql = join("\n", $va_joins); $va_wheres = array(); if (sizeof($va_restrict_to_relationship_types) > 0 && is_object($t_item_rel)) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id IN (" . join(',', $va_restrict_to_relationship_types) . "))"; } if (sizeof($va_exclude_relationship_types) > 0 && is_object($t_item_rel)) { $va_wheres[] = "(" . $t_item_rel->tableName() . ".type_id NOT IN (" . join(',', $va_exclude_relationship_types) . "))"; } $vs_where_sql = ''; if (sizeof($va_wheres) > 0) { $vs_where_sql = ' AND ' . join(' AND ', $va_wheres); } if ((!isset($va_facet_info['dont_expand_hierarchically']) || !$va_facet_info['dont_expand_hierarchically']) && $t_rel_item->isHierarchical() && $t_rel_item->load((int) $vn_row_id)) { $vs_hier_left_fld = $t_rel_item->getProperty('HIERARCHY_LEFT_INDEX_FLD'); $vs_hier_right_fld = $t_rel_item->getProperty('HIERARCHY_RIGHT_INDEX_FLD'); $vs_get_item_sql = "{$vs_rel_table_name}.{$vs_hier_left_fld} >= " . $t_rel_item->get($vs_hier_left_fld) . " AND {$vs_rel_table_name}.{$vs_hier_right_fld} <= " . $t_rel_item->get($vs_hier_right_fld); if ($vn_hier_id_fld = $t_rel_item->getProperty('HIERARCHY_ID_FLD')) { $vs_get_item_sql .= " AND {$vs_rel_table_name}.{$vn_hier_id_fld} = " . (int) $t_rel_item->get($vn_hier_id_fld); } $vs_get_item_sql = "({$vs_get_item_sql})"; } else { $vs_get_item_sql = "({$vs_rel_table_name}.{$vs_rel_table_pk} = " . (int) $vn_row_id . ")"; } if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\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{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}"; //print "$vs_sql<hr>"; $qr_res = $this->opo_db->query($vs_sql); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_join_sql}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\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{$vs_get_item_sql}\n\t\t\t\t\t\t\t\t\t\t\t\t\t{$vs_where_sql}"; //print "$vs_sql<hr>"; $qr_res = $this->opo_db->query($vs_sql); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- case 'fieldList': $vs_field_name = $va_facet_info['field']; $vs_table_name = $this->ops_browse_table_name; if ($va_facet_info['relative_to']) { if ($va_relative_execute_sql_data = $this->_getRelativeExecuteSQLData($va_facet_info['relative_to'], $pa_options)) { $va_relative_to_join = $va_relative_execute_sql_data['relative_joins']; $vs_relative_to_join = join("\n", $va_relative_to_join); $vs_table_name = $vs_target_browse_table_name = $va_relative_execute_sql_data['target_table_name']; $vs_target_browse_table_num = $va_relative_execute_sql_data['target_table_num']; $vs_target_browse_table_pk = $va_relative_execute_sql_data['target_table_pk']; } } foreach ($va_row_ids as $vn_row_id) { $vn_row_id = urldecode($vn_row_id); if ($vn_i == 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_acc\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_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({$vs_table_name}.{$vs_field_name} = ?)"; //print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_row_id."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); } else { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_tmp"); $vs_sql = "\n\t\t\t\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO ca_browses_tmp\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $this->ops_browse_table_name . "\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_relative_to_join}\n\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_browses_acc ON ca_browses_acc.row_id = " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . "\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({$vs_table_name}.{$vs_field_name} = ?)"; //print "$vs_sql [".intval($this->opn_browse_table_num)."/".$vn_row_id."]<hr>"; $qr_res = $this->opo_db->query($vs_sql, $vn_row_id); $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); $qr_res = $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc SELECT row_id FROM ca_browses_tmp"); } $vn_i++; } break; # ----------------------------------------------------- # ----------------------------------------------------- default: // handle "search" criteria - search engine queries that can be browsed if ($vs_facet_name === '_search') { $qr_res = $this->opo_db->query("TRUNCATE TABLE ca_browses_acc"); if (!($o_search = caGetSearchInstance($this->ops_browse_table_name))) { $this->postError(2900, _t("Invalid search type"), "BrowseEngine->execute()"); break; } $vs_pk = $t_item->primaryKey(); if (is_array($va_type_ids = $this->getTypeRestrictionList()) && sizeof($va_type_ids)) { $o_search->setTypeRestrictions($va_type_ids); } $va_options = $pa_options; unset($va_options['sort']); // browse engine takes care of sort so there is no reason to waste time having the search engine do so $va_options['filterNonPrimaryRepresentations'] = true; // filter out non-primary representations in ca_objects results to save (a bit) of time $qr_res = $o_search->search($va_row_ids[0], $va_options); if ($qr_res->numHits() > 0) { $va_ids = array(); $va_id_list = $qr_res->getPrimaryKeyValues(); foreach ($va_id_list as $vn_id) { $va_ids[] = "({$vn_id})"; } $this->opo_db->query("INSERT IGNORE INTO ca_browses_acc VALUES " . join(",", $va_ids)); $vn_i++; } } else { $this->postError(2900, _t("Invalid criteria type"), "BrowseEngine->execute()"); } break; # ----------------------------------------------------- } } $vs_filter_join_sql = $vs_filter_where_sql = ''; $va_wheres = array(); $va_joins = array(); $vs_sql_distinct = ''; if (sizeof($this->opa_result_filters)) { $va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id'; $va_tmp = array(); foreach ($this->opa_result_filters as $va_filter) { $vm_val = $this->_filterValueToQueryValue($va_filter); $va_wheres[] = $this->ops_browse_table_name . '.' . $va_filter['field'] . " " . $va_filter['operator'] . " " . $vm_val; } } if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) { $va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id'; $va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) { if (!isset($va_joins[$this->ops_browse_table_name])) { $va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id'; } $va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)"; } if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) { $o_tep = new TimeExpressionParser(); if ($o_tep->parse($pa_options['limitToModifiedOn'])) { $va_range = $o_tep->getUnixTimestamps(); $va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = ca_browses_acc.row_id AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum(); $va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id"; $va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))"; $vs_sql_distinct = 'DISTINCT'; // need to pull distinct rows since joining the change log can cause dupes } } if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) { $t_subject = $this->getSubjectInstance(); $va_joins[$this->ops_browse_table_name] = "INNER JOIN " . $this->ops_browse_table_name . " ON " . $this->ops_browse_table_name . '.' . $t_item->primaryKey() . ' = ca_browses_acc.row_id'; $va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . '))'; } if (sizeof($va_wheres)) { $vs_filter_where_sql = 'WHERE ' . join(' AND ', $va_wheres); } if (sizeof($va_joins)) { $vs_filter_join_sql = join("\n", $va_joins); } $qr_res = $this->opo_db->query("\n\t\t\t\t\t\tSELECT {$vs_sql_distinct} row_id\n\t\t\t\t\t\tFROM ca_browses_acc\n\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t"); while ($qr_res->nextRow()) { $va_results[] = $qr_res->get('row_id', array('binary' => true)); } $this->_dropTempTable('ca_browses_acc'); $this->_dropTempTable('ca_browses_tmp'); if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) { $va_results = array_keys($this->filterHitsByACL(array_flip($va_results), $vn_user_id, __CA_ACL_READONLY_ACCESS__)); } $this->opo_ca_browse_cache->setResults($va_results); $vb_need_to_save_in_cache = true; } } else { // no criteria - don't try to find anything unless configured to do so $va_settings = $this->opo_ca_browse_config->getAssoc($this->ops_browse_table_name); if (isset($va_settings['show_all_for_no_criteria_browse']) && $va_settings['show_all_for_no_criteria_browse']) { $va_wheres = $va_joins = array(); $vs_pk = $t_item->primaryKey(); if (isset($pa_options['checkAccess']) && is_array($pa_options['checkAccess']) && sizeof($pa_options['checkAccess']) && $t_item->hasField('access')) { $va_wheres[] = "(" . $this->ops_browse_table_name . ".access IN (" . join(',', $pa_options['checkAccess']) . "))"; } if ((!isset($pa_options['showDeleted']) || !$pa_options['showDeleted']) && $t_item->hasField('deleted')) { $va_wheres[] = "(" . $this->ops_browse_table_name . ".deleted = 0)"; } if (isset($pa_options['limitToModifiedOn']) && $pa_options['limitToModifiedOn']) { $o_tep = new TimeExpressionParser(); if ($o_tep->parse($pa_options['limitToModifiedOn'])) { $va_range = $o_tep->getUnixTimestamps(); $va_joins['ca_change_log_subjects'] = "INNER JOIN ca_change_log_subjects ON ca_change_log_subjects.subject_row_id = " . $this->ops_browse_table_name . ".{$vs_pk} AND ca_change_log_subjects.subject_table_num = " . $t_item->tableNum(); $va_joins['ca_change_log'] = "INNER JOIN ca_change_log ON ca_change_log.log_id = ca_change_log_subjects.log_id"; $va_wheres[] = "(((ca_change_log.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ") AND (ca_change_log.changetype IN ('I', 'U', 'D'))))"; $vs_sql_distinct = 'DISTINCT'; // need to pull distinct rows since joining the change log can cause dupes } } if (($va_browse_type_ids = $this->getTypeRestrictionList()) && sizeof($va_browse_type_ids)) { $t_subject = $this->getSubjectInstance(); $va_wheres[] = '(' . $this->ops_browse_table_name . '.' . $t_subject->getTypeFieldName() . ' IN (' . join(', ', $va_browse_type_ids) . '))'; } if (sizeof($va_wheres)) { $vs_filter_where_sql = 'WHERE ' . join(' AND ', $va_wheres); } if (sizeof($va_joins)) { $vs_filter_join_sql = join("\n", $va_joins); } $qr_res = $this->opo_db->query("\n\t\t\t\t\t\tSELECT {$vs_pk}\n\t\t\t\t\t\tFROM " . $t_item->tableName() . "\n\t\t\t\t\t\t{$vs_filter_join_sql}\n\t\t\t\t\t\t{$vs_filter_where_sql}\n\t\t\t\t\t"); $va_results = $qr_res->getAllFieldValues($vs_pk); if ((!isset($pa_options['dontFilterByACL']) || !$pa_options['dontFilterByACL']) && $this->opo_config->get('perform_item_level_access_checking') && method_exists($t_item, "supportsACL") && $t_item->supportsACL()) { $va_results = array_keys($this->filterHitsByACL(array_flip($va_results), $vn_user_id, __CA_ACL_READONLY_ACCESS__)); } $this->opo_ca_browse_cache->setResults($va_results); } else { $this->opo_ca_browse_cache->setResults(array()); } $vb_need_to_save_in_cache = true; } if ($vb_need_to_cache_facets) { if (!$pa_options['dontCheckFacetAvailability']) { $this->loadFacetContent($pa_options); } } if ($vb_need_to_save_in_cache) { $this->opo_ca_browse_cache->save(); } return true; }