public function getListQuery() { $query = parent::getListQuery(); $query->select('*'); $query->from('#__jmm_canned_queries'); $published = $this->getState('filter.published'); if ($published == '') { $query->where('published IN(1,0)'); } else { if ($published != '*') { $published = (int) $published; $query->where("published='{$published}'"); } } $search = $this->getState('filter.search'); $database = $this->getState('filter.database'); if ($database != '') { $query->where("dbname='{$database}'"); } $db = $this->getDbo(); if (!empty($search)) { $search = '%' . $db->getEscaped($search, true) . '%'; $field_searches = "(title LIKE '{$search}' OR dbname LIKE '{$search}' OR query LIKE '{$search}')"; $query->where($field_searches); } $orderCol = $this->getState('list.ordering'); $orderDirn = $this->getState('list.direction'); if (isset($orderCol)) { $query->order($db->getEscaped($orderCol . ' ' . $orderDirn)); } else { $query->order('id desc'); } return $query; }
public function getListQuery() { $tbl = JRequest::getString('tbl', '#__users'); $query = parent::getListQuery(); $query->select('*'); $query->from($tbl); $search = $this->getState('filter.search'); //$db = $this -> getDbo(); $this->_db = JMMCommon::getDBInstance(); /* if (!empty($search)) { $search = '%' . $db -> getEscaped($search, true) . '%'; $fileds = JMMCommon::getCloumnsFromTable($tbl); $searchflString = implode(" LIKE '$search' OR ", $fileds); $field_searches = "($searchflString)"; $query -> where($field_searches); } */ $filter_order = JRequest::getString('filter_order', null); $filter_order_Dir = JRequest::getString('filter_order_Dir', null); if (!empty($filter_order) && !empty($filter_order_Dir)) { $query->order($filter_order . ' ' . $filter_order_Dir); } 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 = JFactory::getDbo(); $query = parent::getListQuery(); $query->select(array('l.lang_code', 'l.published', 'l.title', 'l.image', 'tr.state', 'SUM(tr.word_counter) AS word_count', 'lc.comment'))->from('#__languages AS l')->leftJoin('#__neno_language_external_translators_comments AS lc ON l.lang_code = lc.language')->leftJoin('#__neno_content_element_translations AS tr ON tr.language = l.lang_code')->where('l.lang_code <> ' . $db->quote(NenoSettings::get('source_language')))->group(array('l.lang_code', 'tr.state'))->order('lang_code'); return $query; }
function getListQuery() { $db = $this->getDbo(); $query = parent::getListQuery(); $input = JFactory::getApplication()->input->getInt('trieda_id', 0); $query->select('id, meno ,priezvisko')->from('#__absolventi_ziaci')->where('trieda' . ' = ' . $input); return $query; }
public function getListQuery() { $query = parent::getListQuery(); //$db = $this->getDbo(); //$query = $db->getQuery(true); $query->select('titel, artist'); $query->from('#__pa_videolesson')->order('antal_visningar DESC'); return $query; }
/** * The List Query * params used when loaded from modules */ function getListQuery($catids = null, $order = null, $limit = null) { // db helper $db = $this->_db; // params $app = JFactory::getApplication(); $menu = $app->getMenu(); $active = $menu->getActive() ? $menu->getActive() : $menu->getDefault(); $params = $menu->getParams($active->id); // query $query = parent::getListQuery(); $query->select('a.*')->from('#__jkit_items AS a')->where('a.state = 1'); $query->select('c.title AS category')->leftJoin('#__categories AS c ON c.id = a.catid'); $query->select('u.title AS user, u.alias AS user_alias')->leftJoin('#__jkit_users AS u ON u.id = a.user_id'); // category $catids = $catids ? $catids : $params->get('categories'); $categories = implode(',', (array) $catids); $query->where("a.catid IN ({$categories})"); // tag view? $tag = $this->getTag(); if ($tag) { $query->leftJoin('#__jkit_tags_item AS t ON t.item_id = a.id'); $query->where("t.tag_id = {$tag->id}"); } // archive view? $archive = $this->getArchive(); if ($archive) { $start = $db->quote($archive->start); $end = $db->quote($archive->end); $query->where("a.created >= {$start}"); $query->where("a.created <= {$end}"); } // author view? $author = $this->getAuthor(); if ($author) { $query->where("a.user_id = {$author->id}"); } // ordering $order = $order ? $order : $params->get('order', 'new'); if ($order == 'new') { $ordering = 'a.created DESC'; } elseif ($order == 'old') { $ordering = 'a.created ASC'; } elseif ($order == 'asc') { $ordering = 'a.ordering ASC'; } elseif ($order == 'desc') { $ordering = 'a.ordering DESC'; } $this->setState('list.ordering', $ordering); $this->setState('list.direction', ''); $query->order($ordering); // limit $limit = $limit ? $limit : $params->get('leading') + $params->get('primary') + $params->get('secondary'); $this->setState('list.limit', $limit); 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 = JFactory::getDbo(); $query = parent::getListQuery(); $query->select('a.*')->from('#__neno_settings AS a')->where(array('a.setting_key NOT LIKE ' . $db->quote('%installation%'), 'a.setting_key NOT LIKE ' . $db->quote('%setup%'), 'a.setting_key NOT LIKE ' . $db->quote('%discovering%'))); // Add the list ordering clause. $orderCol = $this->state->get('list.ordering'); $orderDirection = $this->state->get('list.direction'); if ($orderCol && $orderDirection) { $query->order($db->escape($orderCol . ' ' . $orderDirection)); } return $query; }
/** * Get the master query for retrieving a list of comment subject to the model state. * * @return JDatabaseQuery * */ protected function getListQuery() { $query = parent::getListQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.id, a.content_id, a.visitor_name, a.state, ' . 'a.visitor_email, a.visitor_comments, a.created')); $query->from('#__mjcomments AS a'); // Join over the article. $query->select('c.title AS content_title, c.alias AS content_alias, c.catid AS content_category, c.language AS content_language')->join('LEFT', '#__content AS c ON c.id = a.content_id'); // Filter by a single article. $contentId = $this->getState('filter.content_id'); if (is_numeric($contentId)) { $query->where('a.content_id = ' . (int) $contentId); } // Filter by published state $published = $this->getState('filter.published'); if (is_numeric($published)) { $query->where('a.state = ' . (int) $published); } // Add the list ordering clause. $query->order($this->getState('list.ordering', 'a.created') . ' ' . $this->getState('list.direction', 'DESC')); return $query; }
function getListQuery() { $query = parent::getListQuery(); $query->select('tr.id, tr.rok_nastupu, tr.rok_vystupu, tr.trieda, uc.meno, uc.priezvisko, tr.tablo_url')->from('#__absolventi_triedy as tr')->join('INNER', '#__absolventi_ucitelia as uc ON tr.triedny = uc.id')->order($this->getState('list.ordering', 'default_sort_column') . ' ' . $this->getState('list.direction', 'ASC')); return $query; }
public function getListQuery() { $query = parent::getListQuery(); $query->select('id')->from('jos_dbtest'); return $query; }
/** * Build an SQL query to load the list data. * * @return JDatabaseQuery * * @since 1.6 */ protected function getListQuery() { $query = parent::getListQuery(); $query->select(array('SUM(word_counter) AS words', 'trtm.translation_method_id', 'l.title_native', 'l.image', 'language'))->from('#__neno_content_element_translations AS tr')->innerJoin('#__neno_content_element_translation_x_translation_methods AS trtm ON trtm.translation_id = tr.id')->innerJoin('#__neno_translation_methods AS tm ON trtm.translation_method_id = tm.id')->leftJoin('#__languages AS l ON tr.language = l.lang_code')->where(array('state = ' . NenoContentElementTranslation::NOT_TRANSLATED_STATE, 'NOT EXISTS (SELECT 1 FROM #__neno_jobs_x_translations AS jt WHERE tr.id = jt.translation_id)', 'tm.pricing_per_word <> 0', 'trtm.ordering = 1'))->group(array('trtm.translation_method_id', 'language')); return $query; }
function getListQuery() { $query = parent::getListQuery(); $query->select('id, meno ,priezvisko')->from('#__absolventi_ucitelia')->order($this->getState('list.ordering', 'default_sort_column') . ' ' . $this->getState('list.direction', 'ASC')); return $query; }
/** * Build an SQL query to load the list data. * * @return JDatabaseQuery * * @since 1.6 */ protected function getListQuery() { $db = JFactory::getDbo(); $workingLanguage = NenoHelper::getWorkingLanguage(); // Create a new query object. $dbStrings = parent::getListQuery(); $languageFileStrings = parent::getListQuery(); $dbStrings->select(array('tr1.*', 'f.field_name AS `key`', 't.table_name AS element_name', 'g1.group_name AS `group`', 'CHAR_LENGTH(tr1.string) AS characters'))->from('`#__neno_content_element_translations` AS tr1')->innerJoin('`#__neno_content_element_fields` AS f ON tr1.content_id = f.id')->innerJoin('`#__neno_content_element_tables` AS t ON t.id = f.table_id')->innerJoin('`#__neno_content_element_groups` AS g1 ON t.group_id = g1.id ')->where(array('tr1.language = ' . $db->quote($workingLanguage), 'tr1.content_type = ' . $db->quote('db_string'), 'f.translate = 1'))->group(array('HEX(tr1.string)', 'tr1.state'))->order('tr1.id'); $languageFileStrings->select(array('tr2.*', 'ls.constant AS `key`', 'lf.filename AS element_name', 'g2.group_name AS `group`', 'CHAR_LENGTH(tr2.string) AS characters'))->from('`#__neno_content_element_translations` AS tr2')->innerJoin('`#__neno_content_element_language_strings` AS ls ON tr2.content_id = ls.id')->innerJoin('`#__neno_content_element_language_files` AS lf ON lf.id = ls.languagefile_id')->innerJoin('`#__neno_content_element_groups` AS g2 ON lf.group_id = g2.id ')->where(array('tr2.language = ' . $db->quote($workingLanguage), 'tr2.content_type = ' . $db->quote('lang_string')))->group(array('HEX(tr2.string)', 'tr2.state'))->order('tr2.id'); $queryWhereDb = array(); /* @var $groups array */ $groups = $this->getState('filter.group_id', array()); /* @var $element array */ $element = $this->getState('filter.element', array()); /* @var $field array */ $field = $this->getState('filter.field', array()); /* @var $file array */ $file = $this->getState('filter.files', array()); $groupIdAdded = false; if (!is_array($groups)) { $groups = array($groups); } if (!empty($groups) && !in_array('none', $groups)) { $queryWhereDb[] = 't.group_id IN (' . implode(', ', $groups) . ')'; $languageFileStrings->where('lf.group_id IN (' . implode(', ', $groups) . ')'); } if (!empty($element)) { if ($groupIdAdded === false) { $languageFileStrings->select('g2.id AS group_id'); $dbStrings->select('g1.id AS group_id'); $groupIdAdded = true; } $queryWhereDb[] = 't.id IN (' . implode(', ', $element) . ')'; // Do not show any strings for this language file if (empty($file)) { $languageFileStrings->where('lf.id = 0'); } } if (!empty($field)) { if ($groupIdAdded === false) { $languageFileStrings->select('g2.id AS group_id'); $dbStrings->select('g1.id AS group_id'); } // Do not show any strings for this language file if (empty($file)) { $languageFileStrings->where('lf.id = 0'); } $queryWhereDb[] = 'f.id IN (' . implode(', ', $field) . ')'; } if (!empty($file)) { $languageFileStrings->where('lf.id IN (' . implode(',', $file) . ')'); if (empty($field) && empty($element)) { $queryWhereDb[] = 'f.id = 0 AND t.id = 0'; } } if (count($queryWhereDb)) { $dbStrings->where('(' . implode(' OR ', $queryWhereDb) . ')'); } $method = (array) $this->getState('filter.translator_type', array()); if (!empty($method) && !in_array('none', $method)) { $dbStrings->where('tr_x_tm1.translation_method_id IN ("' . implode('", "', $method) . '")')->leftJoin('`#__neno_content_element_translation_x_translation_methods` AS tr_x_tm1 ON tr1.id = tr_x_tm1.translation_id'); $languageFileStrings->where('tr_x_tm2.translation_method_id IN ("' . implode('", "', $method) . '")')->leftJoin('`#__neno_content_element_translation_x_translation_methods` AS tr_x_tm2 ON tr2.id = tr_x_tm2.translation_id'); } $status = (array) $this->getState('filter.translation_status', array()); if (!empty($status) && $status[0] !== '' && !in_array('none', $status)) { $dbStrings->where('tr1.state IN (' . implode(', ', $status) . ')'); $languageFileStrings->where('tr2.state IN (' . implode(', ', $status) . ')'); } // Hide empty strings if the user wants to do that if (NenoSettings::get('hide_empty_strings', true)) { $dbStrings->where('tr1.original_text <> ' . $db->quote('')); $languageFileStrings->where('tr2.original_text <> ' . $db->quote('')); } $query = parent::getListQuery(); $query->select('DISTINCT *')->from('((' . (string) $dbStrings . ') UNION (' . (string) $languageFileStrings . ')) AS a')->group('id'); $search = $this->getState('filter.search'); if (!empty($search)) { $search = $db->quote('%' . $search . '%'); $query->where('(a.original_text LIKE ' . $search . ' OR a.string LIKE ' . $search . ')'); } // Add the list ordering clause. $orderCol = $this->state->get('list.ordering'); $orderDirection = $this->state->get('list.direction'); if ($orderCol && $orderDirection) { $query->order($db->escape($orderCol . ' ' . $orderDirection)); } return $query; }