PHP flexicontent_db::buildItemOrderBy方法代码示例

 static function createItemsListSQL(&$params, &$_item_data = null, $isform = 0, $reverse_field = 0, &$parentfield, &$parentitem)
 {
     $db = JFactory::getDBO();
     $sfx = $isform ? '_form' : '';
     // Get data like aliases and published state
     $publish_where = '';
     if ($params->get('use_publish_dates', 1)) {
         // Date-Times are stored as UTC, we should use current UTC time to compare and not user time (requestTime),
         //  thus the items are published globally at the time the author specified in his/her local clock
         //$app  = JFactory::getApplication();
         //$now  = FLEXI_J16GE ? $app->requestTime : $app->get('requestTime');   // NOT correct behavior it should be UTC (below)
         //$date = JFactory::getDate();
         //$now  = FLEXI_J16GE ? $date->toSql() : $date->toMySQL();              // NOT good if string passed to function that will be cached, because string continuesly different
         $_nowDate = 'UTC_TIMESTAMP()';
         //$db->Quote($now);
         $nullDate = $db->getNullDate();
         $publish_where = ' AND ( i.publish_up = ' . $db->Quote($nullDate) . ' OR i.publish_up <= ' . $_nowDate . ' )';
         $publish_where .= ' AND ( i.publish_down = ' . $db->Quote($nullDate) . ' OR i.publish_down >= ' . $_nowDate . ' )';
     }
     // item IDs via reversing a relation field
     if ($reverse_field) {
         $item_join = ' JOIN #__flexicontent_fields_item_relations AS fi_rel' . '  ON i.id=fi_rel.item_id AND fi_rel.field_id=' . $reverse_field . ' AND CAST(fi_rel.value AS UNSIGNED)=' . $parentitem->id;
     } else {
         $item_where = ' AND i.id IN (' . implode(",", array_keys($_item_data)) . ')';
     }
     // Get orderby SQL CLAUSE ('ordering' is passed by reference but no frontend user override is used (we give empty 'request_var')
     $order = $params->get('orderby' . $sfx, 'alpha');
     $orderby = flexicontent_db::buildItemOrderBy($params, $order, $request_var = '', $config_param = '', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', '', '', $sfx, $support_2nd_lvl = true);
     $orderby_join = '';
     // Create JOIN for ordering items by a custom field (use SFC)
     if ('field' == $order[1]) {
         $orderbycustomfieldid = (int) $params->get('orderbycustomfieldid' . $sfx, 0);
         $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f ON f.item_id = i.id AND f.field_id=' . $orderbycustomfieldid;
     }
     // Create JOIN for ordering items by a custom field (Level 2)
     if ($sfx == '' && 'field' == $order[2]) {
         $orderbycustomfieldid_2nd = (int) $params->get('orderbycustomfieldid' . '_2nd', 0);
         $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f2 ON f2.item_id = i.id AND f2.field_id=' . $orderbycustomfieldid_2nd;
     }
     // Create JOIN for ordering items by a most commented
     if (in_array('commented', $order)) {
         $orderby_col = ', count(com.object_id) AS comments_total';
         $orderby_join .= ' LEFT JOIN #__jcomments AS com ON com.object_id = i.id';
     }
     // Create JOIN for ordering items by a most rated
     if (in_array('rated', $order)) {
         $orderby_col = ', (cr.rating_sum / cr.rating_count) * 20 AS votes';
         $orderby_join .= ' LEFT JOIN #__content_rating AS cr ON cr.content_id = i.id';
     }
     // Create JOIN for ordering items by author name
     if (in_array('author', $order) || in_array('rauthor', $order)) {
         $orderby_join .= ' LEFT JOIN #__users AS u ON u.id = i.created_by';
     }
     // Because query includes specific items it should be fast
     $query = 'SELECT i.*, ext.type_id,' . ' GROUP_CONCAT(c.id SEPARATOR  ",") AS catidlist, ' . ' GROUP_CONCAT(c.alias SEPARATOR  ",") AS  cataliaslist ' . @$orderby_col . ' FROM #__content AS i ' . ' LEFT JOIN #__flexicontent_items_ext AS ext ON i.id=ext.item_id ' . @$item_join . @$orderby_join . ' LEFT JOIN #__flexicontent_cats_item_relations AS rel ON i.id=rel.itemid ' . ' LEFT JOIN #__categories AS c ON c.id=rel.catid ' . ' WHERE 1 ' . @$item_where . $publish_where . ' GROUP BY i.id ' . $orderby;
     //echo "<pre>".$query."</pre>";
     return $query;
 }

 /**
  * Build the order clause for item listing
  *
  * @access private
  * @return string
  */
 function _buildItemOrderBy(&$order = '')
 {
     $request_var = $this->_params->get('orderby_override') ? 'orderby' : '';
     $default_order = $this->getState('filter_order');
     $default_order_dir = $this->getState('filter_order_Dir');
     // Precedence: $request_var ==> $order ==> $config_param ==> $default_order
     return flexicontent_db::buildItemOrderBy($this->_params, $order, $request_var, $config_param = 'orderby', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', $default_order, $default_order_dir, $sfx = '', $support_2nd_lvl = true);
 }

//.........这里部分代码省略.........
                     // [-3d,+3d] days of month, IGNORE  MONTH, YEAR
                     $where .= ' AND ((DAYOFMONTH(' . $db->Quote($cdate) . ')-3) <= DAYOFMONTH(' . $comp . ') AND DAYOFMONTH(' . $comp . ') <= (DAYOFMONTH(' . $db->Quote($cdate) . ')+4) )';
                     break;
                 case '18':
                     // same week of month, IGNORE MONTH, YEAR
                     $week_start = (int) $params->get('week_start', 0);
                     // 0 is sunday, 5 is monday
                     $week_of_month = '(WEEK(%s,5) - WEEK(DATE_SUB(%s, INTERVAL DAYOFMONTH(%s)-1 DAY),5)+1)';
                     $where .= ' AND (' . str_replace('%s', $comp, $week_of_month) . ' = ' . str_replace('%s', $db->Quote($cdate), $week_of_month) . ' )';
                     break;
             }
         }
     }
     // *****************************
     // EXTRA joins for special cases
     // *****************************
     // EXTRA joins when comparing to custom date field
     $join_date = '';
     if ($behaviour_dates || $method_dates != 1) {
         // using date SCOPE: dynamic behaviour, or static behavior with (static) method != ALL(=1)
         if (($bdate || $edate || $behaviour_dates) && $date_type == 3) {
             if ($datecomp_field) {
                 $join_date = '	LEFT JOIN #__flexicontent_fields_item_relations AS dfrel' . '   ON ( i.id = dfrel.item_id AND dfrel.field_id = ' . $datecomp_field . ' )';
             } else {
                 echo "<b>WARNING:</b> Misconfigured date scope, you have set DATE TYPE as CUSTOM DATE Field, but have not select any specific DATE Field to be used<br/>";
                 //$join_date = '';
                 return;
             }
         }
     }
     // *****************************************************************************************************************************
     // Get orderby SQL CLAUSE ('ordering' is passed by reference but no frontend user override is used (we give empty 'request_var')
     // *****************************************************************************************************************************
     $orderby = flexicontent_db::buildItemOrderBy($params, $ordering, $request_var = '', $config_param = 'ordering', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', $default_order = '', $default_order_dir = '', $sfx = '', $support_2nd_lvl = true);
     //echo "<br/>" . print_r($ordering, true) ."<br/>";
     // EXTRA join of field used in custom ordering
     // NOTE: if (1st/2nd level) custom field id is not set, THEN 'field' ordering was changed to level's default, by the ORDER CLAUSE creating function
     $orderby_join = '';
     // Create JOIN for ordering items by a custom field (Level 1)
     if ('field' == $ordering[1]) {
         $orderbycustomfieldid = (int) $params->get('orderbycustomfieldid', 0);
         $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f ON f.item_id = i.id AND f.field_id=' . $orderbycustomfieldid;
     }
     // Create JOIN for ordering items by a custom field (Level 2)
     if ('field' == $ordering[2]) {
         $orderbycustomfieldid_2nd = (int) $params->get('orderbycustomfieldid' . '_2nd', 0);
         $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f2 ON f2.item_id = i.id AND f2.field_id=' . $orderbycustomfieldid_2nd;
     }
     // Create JOIN for ordering items by author's name
     if (in_array('author', $ordering) || in_array('rauthor', $ordering)) {
         $orderby_join .= ' LEFT JOIN #__users AS u ON u.id = i.created_by';
     }
     // *****************************************************
     // Decide Select Sub-Clause and Join-Clause for comments
     // *****************************************************
     $display_comments = $params->get('display_comments');
     $display_comments_feat = $params->get('display_comments_feat');
     // Check (when needed) if jcomments are installed, and also clear 'commented' ordering if they jcomments is missing
     if ($display_comments_feat || $display_comments || in_array('commented', $ordering)) {
         // Handle jcomments integratio. No need to reset 'commented' ordering if jcomments not installed,
         // and neither print message, the ORDER CLAUSE creating function should have done this already
         if (!file_exists(JPATH_SITE . DS . 'components' . DS . 'com_jcomments' . DS . 'jcomments.php')) {
             //echo "jcomments not installed, you need jcomments to use 'Most commented' ordering OR display comments information.<br>\n";
             $jcomments_exist = false;
         } else {
             $jcomments_exist = true;

//.........这里部分代码省略.........
             case 'any':
             default:
                 if ($filter_word_like_any) {
                     $_text_match = ' LOWER (' . $ts . '.search_index) LIKE ' . $db->Quote('%' . $escaped_text . '%', false);
                 } else {
                     $words = preg_split('/\\s\\s*/u', $text);
                     $stopwords = array();
                     $shortwords = array();
                     if (!$search_prefix) {
                         $words = flexicontent_db::removeInvalidWords($words, $stopwords, $shortwords, $si_tbl, 'search_index', $isprefix = 1);
                     }
                     JRequest::setVar('ignoredwords', implode(' ', $stopwords));
                     JRequest::setVar('shortwords', implode(' ', $shortwords));
                     $newtext = implode('* ', $words) . '*';
                     $quoted_text = $db->escape($newtext, true);
                     $quoted_text = $db->Quote($quoted_text, false);
                     $_text_match = ' MATCH (' . $ts . '.search_index) AGAINST (' . $quoted_text . ' IN BOOLEAN MODE) ';
                 }
                 break;
         }
         // Construct TEXT SEARCH limitation SUB-QUERY (contained in a AND-WHERE clause)
         $text_where = ' AND ' . $_text_match;
     } else {
         $text_where = '';
     }
     // *******************
     // Create ORDER clause
     // *******************
     // FLEXIcontent search view, use FLEXIcontent ordering
     $orderby_join = '';
     $orderby_col = '';
     if (JRequest::getVar('option') == 'com_flexicontent') {
         $order = '';
         $orderby = flexicontent_db::buildItemOrderBy($params, $order, $_request_var = 'orderby', $_config_param = 'orderby', $_item_tbl_alias = 'i', $_relcat_tbl_alias = 'rel', $_default_order = '', $_default_order_dir = '', $sfx = '', $support_2nd_lvl = false);
         // Create JOIN for ordering items by a custom field (Level 1)
         if ('field' == $order[1]) {
             $orderbycustomfieldid = (int) $params->get('orderbycustomfieldid', 0);
             $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f ON f.item_id = i.id AND f.field_id=' . $orderbycustomfieldid;
         }
         // Create JOIN for ordering items by a custom field (Level 2)
         if ('field' == $order[2]) {
             $orderbycustomfieldid_2nd = (int) $params->get('orderbycustomfieldid' . '_2nd', 0);
             $orderby_join .= ' LEFT JOIN #__flexicontent_fields_item_relations AS f2 ON f2.item_id = i.id AND f2.field_id=' . $orderbycustomfieldid_2nd;
         }
         // Create JOIN for ordering items by author's name
         if (in_array('author', $order) || in_array('rauthor', $order)) {
             $orderby_col = '';
             $orderby_join .= ' LEFT JOIN #__users AS u ON u.id = i.created_by';
         }
         // Create JOIN for ordering items by a most commented
         if (in_array('commented', $order)) {
             $orderby_col = ', count(com.object_id) AS comments_total';
             $orderby_join .= ' LEFT JOIN #__jcomments AS com ON com.object_id = i.id';
         }
         // Create JOIN for ordering items by a most rated
         if (in_array('rated', $order)) {
             $orderby_col = ', (cr.rating_sum / cr.rating_count) * 20 AS votes';
             $orderby_join .= ' LEFT JOIN #__content_rating AS cr ON cr.content_id = i.id';
         }
         // Create JOIN for ordering items by their ordering attribute (in item's main category)
         if (in_array('order', $order)) {
             $orderby_join .= ' LEFT JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id AND rel.catid = i.catid';
         }
     } else {
         switch ($ordering) {
             //case 'relevance': $orderby = ' ORDER BY score DESC, i.title ASC'; break;

//.........这里部分代码省略.........
        // TYPE SCOPE
        if (($method_types == 2 || $method_types == 3) && (!count($types) || empty($types[0]))) {
            $field->html = 'Content Type scope is set to include/exclude but no Types are selected in field configuration, please set to "ALL" or select types to include/exclude';
            return;
        }
        if ($method_types == 2) {
            $where[] = ' ie.type_id NOT IN (' . implode(',', $types) . ')';
        } else {
            if ($method_types == 3) {
                $where[] = ' ie.type_id IN (' . implode(',', $types) . ')';
            }
        }
        // include method
        // OTHER SCOPE LIMITS
        if ($samelangonly) {
            $where[] = $item->language == '*' ? " ie.language='*' " : " (ie.language='{$item->language}' OR ie.language='*') ";
        }
        if ($onlypublished) {
            $where[] = " i.state IN (1, -5) ";
        }
        if ($ownedbyuser == 1) {
            $where[] = " i.created_by = " . $user->id;
        } else {
            if ($ownedbyuser == 2) {
                $where[] = " i.created_by = " . $item->created_by;
            }
        }
        $where = !count($where) ? "" : " WHERE " . implode(" AND ", $where);
        // ***********************************************
        // Item retrieving query ... CREATE ORDERBY CLAUSE
        // ***********************************************
        $order = $field->parameters->get('orderby_form', 'alpha');
        // TODO: add more orderings: commented, rated
        $orderby = flexicontent_db::buildItemOrderBy($field->parameters, $order, $request_var = '', $config_param = '', $item_tbl_alias = 'i', $relcat_tbl_alias = 'rel', $default_order = '', $default_order_dir = '', $sfx = '_form', $support_2nd_lvl = false);
        // Create JOIN for ordering items by a most rated
        if (in_array('author', $order) || in_array('rauthor', $order)) {
            $orderby_join = ' LEFT JOIN #__users AS u ON u.id = i.created_by';
        }
        // *****************************************************
        // Item retrieving query ... put together and execute it
        // *****************************************************
        $query = 'SELECT i.title, i.id, i.catid, i.state, i.alias' . ", GROUP_CONCAT(rel.catid SEPARATOR ',') as catlist" . ' FROM #__content AS i ' . ($samelangonly || $method_types > 1 ? " LEFT JOIN #__flexicontent_items_ext AS ie on i.id=ie.item_id " : "") . ' JOIN #__flexicontent_cats_item_relations AS rel on i.id=rel.itemid ' . @$orderby_join . $where . " GROUP BY rel.itemid " . $orderby;
        $db->setQuery($query);
        $items_arr = $db->loadObjectList();
        if ($db->getErrorNum()) {
            echo $db->getErrorMsg();
            $field->html = '';
            return false;
        }
        // *******************************************************
        // Create category tree to use for selecting related items
        // *******************************************************
        require_once JPATH_ROOT . DS . "components" . DS . "com_flexicontent" . DS . "classes" . DS . "flexicontent.categories.php";
        $tree = flexicontent_cats::getCategoriesTree();
        // Get categories without filtering
        if ($allowed_cats) {
            foreach ($allowed_cats as $catid) {
                $allowedtree[$catid] = $tree[$catid];
            }
        }
        if ($disallowed_cats) {
            foreach ($disallowed_cats as $catid) {
                unset($tree[$catid]);
            }
            $allowedtree =& $tree;
        }