PHP flexicontent_db::directQuery方法代码示例

 static function getFilterValuesSearch(&$filter, &$view_join, &$view_where, &$filters_where)
 {
     //echo "<pre>"; debug_print_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); echo "</pre>";
     $db = JFactory::getDBO();
     $filter_where_curr = '';
     foreach ($filters_where as $filter_id => $filter_where) {
         if ($filter_id != $filter->id) {
             $filter_where_curr .= ' ' . $filter_where;
         }
     }
     $valuesselect = @$filter->filter_isindexed ? ' ai.value_id as value, ai.search_index as text ' : ' ai.search_index as value, ai.search_index as text';
     $faceted_filter = $filter->parameters->get('faceted_filter_s', 2);
     $display_filter_as = $filter->parameters->get('display_filter_as_s', 0);
     // Filter Type of Display
     $filter_as_range = in_array($display_filter_as, array(2, 3));
     $show_matching_items = $filter->parameters->get('show_matching_items_s', 1);
     $show_matches = $filter_as_range || !$faceted_filter ? 0 : $show_matching_items;
     static $item_ids_list = null;
     static $item_ids_sub_query = null;
     if ($faceted_filter) {
         // Find items belonging to current view
         if ($item_ids_list === null && empty($view_where)) {
             $item_ids_list = '';
         }
         if ($item_ids_list === null || $item_ids_sub_query === null) {
             $sub_query = 'SELECT DISTINCT ai.item_id ' . "\n" . ' FROM #__flexicontent_advsearch_index AS ai' . "\n" . ' JOIN #__content i ON ai.item_id = i.id' . "\n" . $view_join . "\n" . $view_where . "\n";
             $db->setQuery($sub_query);
             global $fc_run_times, $fc_jprof, $fc_searchview;
             $start_microtime = microtime(true);
             $view_total = 1;
             //(int) @ $fc_searchview['view_total'];
             $use_item_list_below = 0;
             if ($view_total >= $use_item_list_below) {
                 // Use sub-query only if current view has more than 0 items
                 $item_ids_sub_query = $sub_query;
             } else {
                 // If current view has less than nnn items, then pre-calculate an item_id list ... ??? this is may or may not be benfitial ...
                 try {
                     // 1, try to bypass joomla SQL layer, including Falang (slow in large sites, not needed in this query ?) !!
                     $rows = flexicontent_db::directQuery($sub_query, false, true);
                     $item_ids = array();
                     foreach ($rows as $row) {
                         $item_ids[] = $row->id;
                     }
                 } catch (Exception $e) {
                     // 2, get items via normal joomla SQL layer
                     $db->setQuery($sub_query);
                     $item_ids = FLEXI_J16GE ? $db->loadColumn() : $db->loadResultArray();
                     if ($db->getErrorNum()) {
                         JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg()), 'error');
                     }
                 }
                 $item_ids_list = implode(',', $item_ids);
                 unset($item_ids);
             }
             $fc_run_times['_create_filter_init'] = round(1000000 * 10 * (microtime(true) - $start_microtime)) / 10;
         }
         // Get ALL records that have such values for the given field
         $query = 'SELECT ' . $valuesselect . ($faceted_filter && $show_matches ? ', COUNT(DISTINCT ai.item_id) as found ' : '') . "\n" . ' FROM #__flexicontent_advsearch_index AS ai' . "\n" . ' WHERE 1 ' . "\n" . (!$item_ids_list ? '' : ' AND ai.item_id IN(' . $item_ids_list . ')' . "\n") . (!$item_ids_sub_query ? '' : ' AND ai.item_id IN(' . $item_ids_sub_query . ')' . "\n") . ' AND ai.field_id=' . (int) $filter->id . "\n" . str_replace('i.id', 'ai.item_id', $filter_where_curr) . "\n" . ' GROUP BY ai.search_index, ai.value_id' . "\n";
     } else {
         // Get ALL records that have such values for the given field
         $query = 'SELECT ' . $valuesselect . ($faceted_filter && $show_matches ? ', COUNT(DISTINCT i.id) as found ' : '') . "\n" . ' FROM #__flexicontent_advsearch_index AS ai' . "\n" . ' JOIN #__content i ON ai.item_id = i.id' . "\n" . $view_join . "\n" . ' WHERE ' . "\n" . ($view_where ? $view_where . ' AND ' : '') . "\n" . ' ai.field_id=' . (int) $filter->id . "\n" . $filter_where_curr . ' GROUP BY ai.search_index, ai.value_id' . "\n";
         /*$query = 'SELECT DISTINCT '. $valuesselect ."\n"
         		.' FROM #__flexicontent_advsearch_index AS ai'."\n"
         		.' WHERE ai.field_id='.(int)$filter->id."\n"
         		//.' GROUP BY ai.search_index, ai.value_id'."\n"
         		;*/
     }
     $db->setQuery($query);
     $results = $db->loadObjectList('text');
     //echo "<br/>". count($results) ."<br/>";
     //echo nl2br($query) ."<br/><br/>";
     if ($db->getErrorNum()) {
         $filter->html = "Filter for : {$filter->label} cannot be displayed, error during db query :<br />" . $query . "<br/>" . __FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg());
         return array();
     }
     return $results;
 }

//.........这里部分代码省略.........
     $and_where_filters = count($filters_where) ? implode(" ", $filters_where) : '';
     // ************************************************
     // Set variables used by filters creation mechanism
     // ************************************************
     global $fc_searchview;
     $fc_searchview['join_clauses'] = $join_clauses;
     $fc_searchview['join_clauses_with_text'] = $join_clauses_with_text;
     $fc_searchview['where_conf_only'] = $where_conf;
     // WHERE of the view (mainly configuration dependent)
     $fc_searchview['filters_where'] = $filters_where;
     // WHERE of the filters
     $fc_searchview['search'] = $text_where;
     // WHERE for text search
     $fc_searchview['params'] = $params;
     // view's parameters
     // *****************************************************************************************************
     // Execute search query.  NOTE this is skipped it if (a) no text-search and no (b) no filters are active
     // *****************************************************************************************************
     // Do not check for 'contentypes' this are based on configuration and not on form submitted data,
     // considering contenttypes or other configuration based parameters, will return all items on initial search view display !
     if (!count($filters_where) && !strlen($text)) {
         return array();
     }
     $print_logging_info = $params->get('print_logging_info');
     if ($print_logging_info) {
         global $fc_run_times;
         $start_microtime = microtime(true);
     }
     // *****************************************
     // Overcome possible group concat limitation
     // *****************************************
     $query = "SET SESSION group_concat_max_len = 9999999";
     $db->setQuery($query);
     $db->execute();
     // *************
     // Get the items
     // *************
     $query = 'SELECT SQL_CALC_FOUND_ROWS i.id' . $orderby_col . ' FROM #__content AS i' . $join_clauses_with_text . $orderby_join . $joinaccess . $where_conf . $and_where_filters . ' GROUP BY i.id ' . $orderby;
     //echo "Adv search plugin main SQL query: ".nl2br($query)."<br/><br/>";
     // NOTE: The plugin will return a PRECONFIGURED limited number of results, the SEARCH VIEW to do the pagination, splicing (appropriately) the data returned by all search plugins
     try {
         // Get items, we use direct query because some extensions break the SQL_CALC_FOUND_ROWS, so let's bypass them (at this point it is OK)
         // *** Usage of FOUND_ROWS() will fail when (e.g.) Joom!Fish or Falang are installed, in this case we will be forced to re-execute the query ...
         // PLUS, we don't need Joom!Fish or Falang layer at --this-- STEP which may slow down the query considerably in large sites
         $query_limited = $query . ' LIMIT ' . $search_limit . ' OFFSET 0';
         $rows = flexicontent_db::directQuery($query_limited);
         $item_ids = array();
         foreach ($rows as $row) {
             $item_ids[] = $row->id;
         }
         // Get current items total for pagination
         $db->setQuery("SELECT FOUND_ROWS()");
         $fc_searchview['view_total'] = $db->loadResult();
         $app->setUserState('fc_view_total_' . $view, $fc_searchview['view_total']);
     } catch (Exception $e) {
         // Get items via normal joomla SQL layer
         $db->setQuery(str_replace('SQL_CALC_FOUND_ROWS', '', $query), 0, $search_limit);
         $item_ids = $db->loadColumn(0);
     }
     if (!count($item_ids)) {
         return array();
     }
     // No items found
     // *****************
     // Get the item data
     // *****************
     $query_data = 'SELECT i.id, i.title AS title, i.created, i.id AS fc_item_id, i.access, ie.type_id, i.language' . (!$txtmode ? ', ie.search_index AS text' : ', GROUP_CONCAT(ts.search_index ORDER BY f.ordering ASC SEPARATOR \' \') AS text') . ', CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(\':\', i.id, i.alias) ELSE i.id END as slug' . ', CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as categoryslug' . ', CONCAT_WS( " / ", ' . $db->Quote($searchFlexicontent) . ', c.title, i.title ) AS section' . $select_access . ' FROM #__content AS i' . $join_clauses . $join_textsearch . $join_textfields . ' WHERE i.id IN (' . implode(',', $item_ids) . ') ' . ' GROUP BY i.id ' . ' ORDER BY FIELD(i.id, ' . implode(',', $item_ids) . ')';
     //echo nl2br($query)."<br/><br/>";
     $db->setQuery($query_data);
     $list = $db->loadObjectList();
     if ($db->getErrorNum()) {
         echo $db->getErrorMsg();
     }
     if ($print_logging_info) {
         @($fc_run_times['search_query_runtime'] += round(1000000 * 10 * (microtime(true) - $start_microtime)) / 10);
     }
     // *************************************
     // Create item links and other variables
     // *************************************
     //echo "<pre>"; print_r($list); echo "</pre>";
     if ($list) {
         if (count($list) >= $search_limit) {
             $app->setUserState('fc_view_limit_max_' . $view, $search_limit);
         }
         $item_cats = FlexicontentFields::_getCategories($list);
         foreach ($list as $key => $item) {
             $item->text = preg_replace('/\\b' . $search_prefix . '/', '', $item->text);
             $item->categories = isset($item_cats[$item->id]) ? $item_cats[$item->id] : array();
             // in case of item categories missing
             // If joomla article view is allowed allowed and then search view may optional create Joomla article links
             if ($typeData[$item->type_id]->params->get('allow_jview', 0) && $typeData[$item->type_id]->params->get('search_jlinks', 1)) {
                 $item->href = JRoute::_(ContentHelperRoute::getArticleRoute($item->slug, $item->categoryslug, $item->language));
             } else {
                 $item->href = JRoute::_(FlexicontentHelperRoute::getItemRoute($item->slug, $item->categoryslug, 0, $item));
             }
             $item->browsernav = $browsernav;
         }
     }
     return $list;
 }

 /**
  * Method to get Data
  *
  * @access public
  * @return mixed
  */
 function getData()
 {
     $format = JRequest::getCmd('format', null);
     $cparams = $this->_params;
     $print_logging_info = $cparams->get('print_logging_info');
     if ($print_logging_info) {
         global $fc_run_times;
     }
     // Allow limit zero to achieve a category view without items
     $limit = (int) $this->getState('limit');
     $limitstart = (int) $this->getState('limitstart');
     if ($limit <= 0) {
         $this->_data = array();
     } else {
         if ($this->_data === null) {
             if ($print_logging_info) {
                 $start_microtime = microtime(true);
             }
             // Load the content if it doesn't already exist
             // 1, create full query: filter, ordered, limited
             $query = $this->_buildQuery();
             try {
                 // 2, get items, we use direct query because some extensions break the SQL_CALC_FOUND_ROWS, so let's bypass them (at this point it is OK)
                 // *** Usage of FOUND_ROWS() will fail when (e.g.) Joom!Fish or Falang are installed, in this case we will be forced to re-execute the query ...
                 // PLUS, we don't need Joom!Fish or Falang layer at --this-- STEP which may slow down the query considerably in large sites
                 $query_limited = $query . ' LIMIT ' . $limit . ' OFFSET ' . $limitstart;
                 $rows = flexicontent_db::directQuery($query_limited);
                 $query_ids = array();
                 foreach ($rows as $row) {
                     $query_ids[] = $row->id;
                 }
                 //$this->_db->setQuery($query, $limitstart, $limit);
                 //$query_ids = $this->_db->loadColumn();
                 // 3, get current items total for pagination
                 $this->_db->setQuery("SELECT FOUND_ROWS()");
                 $this->_total = $this->_db->loadResult();
             } catch (Exception $e) {
                 // 2, get items via normal joomla SQL layer
                 $this->_db->setQuery($query, $limitstart, $limit);
                 $query_ids = $this->_db->loadColumn();
                 if ($this->_db->getErrorNum()) {
                     JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($this->_db->getErrorMsg()), 'error');
                 }
                 // 3, get current items total for pagination
                 if (count($query_ids)) {
                     if (!$this->_total) {
                         $this->getTotal();
                     }
                 } else {
                     $this->_total = 0;
                 }
             }
             // Assign total number of items found this will be used to decide whether to do item counting per filter value
             global $fc_catview;
             $fc_catview['view_total'] = $this->_total;
             /*if ((int)$this->getState('limitstart') < (int)$this->_total) {
             			$this->_data = $this->_getList( $query, $limitstart, $limit );
             		} else {
             			$this->setState('limitstart',0);
             			$this->setState('start',0);
             			JRequest::setVar('start',0);
             			JRequest::setVar('limitstart',0);
             			$this->_data = $this->_getList( $query, 0, $limit );
             		}*/
             // 4, get item data
             if (count($query_ids)) {
                 $query = $this->_buildQuery($query_ids);
             }
             $_data = array();
             if (count($query_ids)) {
                 $this->_db->setQuery($query);
                 $_data = $this->_db->loadObjectList('id');
                 if ($this->_db->getErrorNum()) {
                     JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($this->_db->getErrorMsg()), 'error');
                 }
             }
             // 5, reorder items
             $this->_data = array();
             if ($_data) {
                 foreach ($query_ids as $item_id) {
                     $this->_data[] = $_data[$item_id];
                 }
             }
             // Get Original content ids for creating some untranslatable fields that have share data (like shared folders)
             flexicontent_db::getOriginalContentItemids($this->_data);
             if ($print_logging_info) {
                 @($fc_run_times['execute_main_query'] += round(1000000 * 10 * (microtime(true) - $start_microtime)) / 10);
             }
         }
     }
     // maybe removed in the future, this is useful in places that item data need to be retrieved again because item object was not given
     global $fc_list_items;
     foreach ($this->_data as $_item) {
         $fc_list_items[$_item->id] = $_item;
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                     // then the item is inside in an unpublished ancestor category, thus inaccessible
                     /*$query->select('CASE WHEN badcats.id is null THEN 1 ELSE 0 END AS ancestor_cats_published');
                     		$subquery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ';
                     		$subquery .= 'ON cat.lft BETWEEN parent.lft AND parent.rgt ';
                     		$subquery .= 'WHERE parent.extension = ' . $db->Quote('com_content');
                     		$subquery .= ' AND parent.published <= 0 GROUP BY cat.id)';
                     		$query->join('LEFT', $subquery . ' AS badcats ON badcats.id = c.id');*/
                     if ($version) {
                         // NOTE: version_id is used by field helper file to load the specified version, the reason for left join here is to verify that the version exists
                         $query->join('LEFT', '#__flexicontent_versions AS ver ON ver.item_id = i.id AND ver.version_id = ' . $db->Quote($version));
                     }
                     // Join on contact table, to get contact data of author
                     //$query = 'SHOW TABLES LIKE "' . JFactory::getApplication()->getCfg('dbprefix') . 'contact_details"';
                     //$db->setQuery($query);
                     //$contact_details_tbl_exists = (boolean) count($db->loadObjectList());
                     //if ( $contact_details_tbl_exists) {
                     //	$query->select('contact.id as contactid' ) ;
                     //	$query->join('LEFT','#__contact_details AS contact on contact.user_id = i.created_by');
                     //}
                     // Join over the categories to get parent category titles
                     //$query->select('parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias');
                     //$query->join('LEFT', '#__categories as parent ON parent.id = c.parent_id');
                     $query->where('i.id = ' . (int) $this->_id);
                     //echo $db->replacePrefix($query);
                 } else {
                     // NOTE: version_id is used by field helper file to load the specified version, the reason for left join here is to verify that the version exists
                     $version_join = $version ? ' LEFT JOIN #__flexicontent_versions AS ver ON ver.item_id = i.id AND ver.version_id = ' . $db->Quote($version) : '';
                     $where = $this->_buildItemWhere();
                     $query = 'SELECT i.*, ie.*, ' . $select_access . ($version ? 'ver.version_id,' : '') . ' c.id AS catid, i.catid as maincatid,' . ' c.published AS catpublished,' . ' c.title AS category_title, c.alias AS category_alias,' . ' ty.name as typename, ty.alias as typealias,' . ' u.name AS author, u.usertype,' . ' v.rating_count as rating_count, ROUND( v.rating_sum / v.rating_count ) AS rating, ((v.rating_sum / v.rating_count)*20) as score,' . ' CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(\':\', i.id, i.alias) ELSE i.id END as slug,' . ' CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as categoryslug,' . ' CASE WHEN i.publish_up = ' . $nullDate . ' OR i.publish_up <= ' . $nowDate . ' THEN 0 ELSE 1 END as publication_scheduled,' . ' CASE WHEN i.publish_down = ' . $nullDate . ' OR i.publish_down >= ' . $nowDate . ' THEN 0 ELSE 1 END as publication_expired' . ' FROM #__content AS i' . ' LEFT JOIN #__flexicontent_items_ext AS ie ON ie.item_id = i.id' . ' LEFT JOIN #__flexicontent_types AS ty ON ie.type_id = ty.id' . ' LEFT JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id' . $limit_to_cid . ' LEFT JOIN #__categories AS c ON c.id = rel.catid' . ' LEFT JOIN #__categories AS mc ON mc.id = i.catid' . ' LEFT JOIN #__users AS u ON u.id = i.created_by' . ' LEFT JOIN #__content_rating AS v ON i.id = v.content_id' . $joinaccess . $version_join . $where;
                 }
                 $db->setQuery($query);
                 // Try to execute query directly and load the data as an object
                 if (FLEXI_FISH && $task == 'edit' && $option == 'com_flexicontent' && in_array($app->getCfg('dbtype'), array('mysqli', 'mysql'))) {
                     $data = flexicontent_db::directQuery($query);
                     $data = @$data[0];
                     //$data = $db->loadObject(null, false);   // do not, translate, this is the JoomFish overridden method of Database extended Class
                 } else {
                     $data = $db->loadObject();
                 }
                 // Check for SQL error
                 if ($db->getErrorNum()) {
                     if (FLEXI_J16GE) {
                         throw new Exception($db->getErrorMsg(), 500);
                     } else {
                         JError::raiseError(500, $db->getErrorMsg());
                     }
                 }
                 //print_r($data); exit;
                 if (!$data) {
                     return false;
                 }
                 // item not found, return
                 if ($version && !$data->version_id) {
                     JError::raiseNotice(10, JText::sprintf('NOTICE: Requested item version %d was not found', $version));
                 }
                 $item =& $data;
             }
             // -- Create the description field called 'text' by appending introtext + readmore + fulltext
             $item->text = $item->introtext;
             $item->text .= JString::strlen(trim($item->fulltext)) ? '<hr id="system-readmore" />' . $item->fulltext : "";
             //echo "<br/>Current version (Frontend Active): " . $item->version;
             //echo "<br/>Version to load: ".$version;
             //echo "<br/><b> *** db title:</b> ".$item->title;
             //echo "<br/><b> *** db text:</b> ".$item->text;
             //echo "<pre>*** item data: "; print_r($item); echo "</pre>"; exit;
             // Set number of loaded version, IMPORTANT: zero means load unversioned data