/** * @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 = ContentHelperQuery::orderbySecondary($articleOrderby, $articleOrderDate) . ', '; $primary = ContentHelperQuery::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 $query->select(' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug'); $query->select(' CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as catslug'); // Filter on month, year // First, get the date field $queryDate = ContentHelperQuery::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; }
/** * Get the master query for retrieving a list of articles subject to the model state. * * @return JDatabaseQuery * * @since 1.6 */ protected 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 = ContentHelperQuery::orderbySecondary($articleOrderby, $articleOrderDate) . ', '; $primary = ContentHelperQuery::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', '!=', '0'); $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', '!=', '0'); $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 = ContentHelperQuery::getQueryDate($articleOrderDate); if ($month = $this->getState('filter.month')) { $query->where($query->month($queryDate) . ' = ' . $month); } if ($year = $this->getState('filter.year')) { $query->where($query->year($queryDate) . ' = ' . $year); } return $query; }