/**
* 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;
}
