/** * @return JDatabaseQuery */ function getListQuery() { // Set the archive ordering $params = $this->state->params; $articleOrderby = $params->get('orderby_sec', 'rdate'); $articleOrderDate = $params->get('order_date'); // No category ordering $categoryOrderby = ''; $secondary = TZ_PortfolioHelperQuery::orderbySecondary($articleOrderby, $articleOrderDate) . ', '; $primary = TZ_PortfolioHelperQuery::orderbyPrimary($categoryOrderby); $orderby = $primary . ' ' . $secondary . ' a.created DESC '; $this->setState('list.ordering', $orderby); $this->setState('list.direction', ''); // Create a new query object. $query = parent::getListQuery(); // Add routing for archive //sqlsrv changes $case_when = ' CASE WHEN '; $case_when .= $query->charLength('a.alias'); $case_when .= ' THEN '; $a_id = $query->castAsChar('a.id'); $case_when .= $query->concatenate(array($a_id, 'a.alias'), ':'); $case_when .= ' ELSE '; $case_when .= $a_id . ' END as slug'; $query->select($case_when); $case_when = ' CASE WHEN '; $case_when .= $query->charLength('c.alias'); $case_when .= ' THEN '; $c_id = $query->castAsChar('c.id'); $case_when .= $query->concatenate(array($c_id, 'c.alias'), ':'); $case_when .= ' ELSE '; $case_when .= $c_id . ' END as catslug'; $query->select($case_when); // Filter on month, year // First, get the date field $queryDate = TZ_PortfolioHelperQuery::getQueryDate($articleOrderDate); if ($month = $this->getState('filter.month')) { $query->where('MONTH(' . $queryDate . ') = ' . $month); } if ($year = $this->getState('filter.year')) { $query->where('YEAR(' . $queryDate . ') = ' . $year); } //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
/** * @return JDatabaseQuery */ function getListQuery() { // Set the blog ordering $params = $this->state->params; $articleOrderby = $params->get('orderby_sec', 'rdate'); $articleOrderDate = $params->get('order_date'); $categoryOrderby = $params->def('orderby_pri', ''); $secondary = TZ_PortfolioHelperQuery::orderbySecondary($articleOrderby, $articleOrderDate) . ', '; $primary = TZ_PortfolioHelperQuery::orderbyPrimary($categoryOrderby); $orderby = $primary . ' ' . $secondary . ' a.created DESC '; $this->setState('list.ordering', $orderby); $this->setState('list.direction', ''); // Create a new query object. $query = parent::getListQuery(); // Filter by frontpage. if ($this->getState('filter.frontpage')) { $query->join('INNER', '#__content_frontpage AS fp ON fp.content_id = a.id'); } // Filter by categories if (is_array($featuredCategories = $this->getState('filter.frontpage.categories'))) { $featuredCategories = array_filter($featuredCategories); $query->where(' a.catid IN (' . implode(',', $featuredCategories) . ')'); } return $query; }