/** * Method to cache the last query constructed. * * This method ensures that the query is constructed only once for a given state of the model. * * @return JDatabaseQuery A JDatabaseQuery object */ public function getListQuery() { // Required objects $db = JFactory::getDbo(); // Build the query $query = $db->getQuery(true)->select('s.*')->select('c.name AS country_name')->select('st.name AS state_name')->from('#__jab_speakers AS s')->leftJoin($db->quoteName('#__jab_countries', 'c') . ' ON s.country_id = c.id')->leftJoin($db->quoteName('#__jab_states', 'st') . ' ON s.state_id = st.id')->where('s.id <> 0'); // Join over the users for the checked out user. $query->select('u.name AS checked_out'); $query->leftJoin('#__users AS u ON u.id = s.checked_out'); // Join over the users for the author. $query->select('ua.name AS created_by'); $query->leftJoin('#__users AS ua ON ua.id = s.created_by'); // Join over the users for the editor. $query->select('ub.name AS modified_by'); $query->leftJoin('#__users AS ub ON ub.id = s.modified_by'); // Join language $query->select('l.title AS language_title'); $query->join('LEFT', $db->quoteName('#__languages') . ' AS l ON l.lang_code = s.language'); // Filter on the language. if ($language = $this->getState('filter.language')) { $query->where('s.language = ' . $db->quote($language)); } // Filter: like / search $search = $this->getState('filter.search'); if (!empty($search)) { $like = '%' . $search . '%'; $query->where('(' . 's.name LIKE ' . $db->quote($like) . ' OR c.name LIKE ' . $db->quote($like) . ' OR st.name LIKE ' . $db->quote($like) . ')'); } // Filter: published $published = RHelperString::multipleSanitised($this->getState('filter.published')); if (!empty($published)) { if (count($published) == 1) { if (is_numeric($published[0])) { $query->where('s.published = ' . (int) $published[0]); } } else { $query->where('s.published IN (' . implode(',', $published) . ')'); } } // Get the ordering modifiers $orderList = $this->getState('list.ordering'); $directionList = $this->getState('list.direction'); $random = $this->getState('list.random'); // Random order selected if ($orderList == 'random') { $orderList = 'RAND()'; } $order = !empty($orderList) ? $orderList : 's.name'; $direction = !empty($directionList) ? $directionList : 'ASC'; $query->order($db->escape($order) . ' ' . $db->escape($direction)); return $query; }
/** * Build an SQL query to load the list data. * * @return JDatabaseQuery * * @since 1.6 */ protected function getListQuery() { // Create a new query object. $db = $this->getDbo(); $query = $db->getQuery(true); // Select the required fields from the table. $query->select("c.*"); $query->from('#__jab_countries AS c'); // Join over the users for the checked out user. $query->select('u.name AS checked_out'); $query->leftJoin('#__users AS u ON u.id = c.checked_out'); // Join over the users for the author. $query->select('ua.name AS created_by'); $query->leftJoin('#__users AS ua ON ua.id = c.created_by'); // Join over the users for the editor. $query->select('ub.name AS modified_by'); $query->leftJoin('#__users AS ub ON ub.id = c.modified_by'); // Join language $query->select('l.title AS language_title'); $query->join('LEFT', $db->quoteName('#__languages') . ' AS l ON l.lang_code = c.language'); // Filter on the language. if ($language = $this->getState('filter.language')) { $query->where('c.language = ' . $db->quote($language)); } // Filter: state $states = RHelperString::multipleSanitised($this->getState('filter.published')); if (!empty($states)) { if (count($states) == 1) { if (is_numeric($states[0])) { $query->where('c.state = ' . (int) $states[0]); } } else { $query->where('c.state IN (' . implode(',', $states) . ')'); } } // Filter: like / search $search = $this->getState('filter.search'); if (!empty($search)) { $like = '%' . $search . '%'; $query->where('(' . 'c.name LIKE ' . $db->quote($like) . ' OR l.title LIKE ' . $db->quote($like) . ')'); } // Get the ordering modifiers $orderList = $this->getState('list.ordering'); $directionList = $this->getState('list.direction'); $random = $this->getState('list.random'); // Random order selected if ($orderList == 'random') { $orderList = 'RAND()'; } $order = !empty($orderList) ? $orderList : 'c.name'; $direction = !empty($directionList) ? $directionList : 'ASC'; $query->order($db->escape($order) . ' ' . $db->escape($direction)); return $query; }