/** * Returns the scheme of the One_Query instance * * @return One_Scheme */ public function getInstanceScheme() { $selects = $this->query->getSelect(); $instanceScheme = $this->query->getScheme(); $usedSchemes = array(); foreach ($selects as $select) { if (preg_match('/([^\\(]+\\()?([^:]+):([^:]+)/', $select, $matches) > 0) { $usedSchemes[] = trim($matches[2]); } } if (count($usedSchemes) == 1) { $this->defineRole($usedSchemes[0]); $instanceScheme = $this->joins[$usedSchemes[0]]['scheme']; } return $instanceScheme; }
/** * Apply filters to the queryscheme * * @param One_Query $q */ public function affect(One_Query $query) { $cx = new One_Context(); $term = $cx->get('term', ''); $field = $cx->get('field', $query->getScheme()->getIdentityAttribute()->getName()); if ('' != trim($term)) { $query->where($field, 'contains', $term); } else { $query->where('isFalse', 'literal', 'false'); } }
/** * Render the query * * @param One_Query $query * @return string */ public function render(One_Query $query, $overrideFilters = false) { $this->query = $query; $this->scheme = $this->query->getScheme(); // if the person wants to perform a raw query, return the raw query if (!is_null($query->getRaw())) { if (One_Config::get('debug.query')) { echo '<pre>'; var_dump($query->getRaw()); echo '</pre>'; } return $query->getRaw(); } $this->query = $query; $this->scheme = $this->query->getScheme(); $resources = $this->scheme->getResources(); // fetch collection to fetch data from $this->_collection = $resources['collection']; // add possible filters to the query if (!$overrideFilters && isset($resources['filter'])) { $filters = explode(';', $resources['filter']); if (count($filters) > 0) { foreach ($filters as $filterName) { if ($filterName != '') { $filter = One_Repository::getFilter($filterName, $query->getScheme()->getName()); $filter->affect($query); } } } } $findQuery = array('fields' => array(), 'query' => array()); if (count($query->getSelect()) > 0) { $findQuery['fields'] = $this->createSelects($query->getSelect()); } // get where clauses $whereClauses = $query->getWhereClauses(); $where = NULL; if (!is_null($whereClauses)) { $where = $this->whereClauses($whereClauses); } if (!is_null($where)) { $findQuery['query'] = $where; } if (One_Config::get('debug.query')) { echo '<pre>'; var_dump($findQuery); echo '</pre>'; } $findQuery = json_encode($findQuery); return $findQuery; }
/** * Apply filters to the queryscheme * * @param One_Query $q */ public function affect(One_Query $query) { $filters = $this->filters(); $searchform = $filters['searchform']; $cx = new One_Context(); $scheme = $query->getScheme(); $idAttr = $scheme->getIdentityAttribute()->getName(); $form = One_Form_Factory::createForm($scheme, $searchform); $widgets = $this->getWidgets($form, $scheme); foreach ($widgets as $widget) { $widget->affectQuery($query); } // for searchresults, results can always be grouped by id, since we only want to show a same result once $query->setGroup($idAttr); }
/** * On selection from a certain scheme and when the feed task is being performed, * select specified attributes needed for creating a feed * * @param One_Query $query */ public function onSelect(One_Query $query) { if ($_REQUEST['task'] == 'feed') { $scheme = $query->getScheme(); $options = $scheme->get('behaviorOptions.' . strtolower($this->getName())); $title = $options['title']; $description = $options['description']; $pubDate = $options['pubDate']; $id = $scheme->getIdentityAttribute()->getName(); $select = array(); $select[] = '`' . $id . '` AS `id`'; if (!is_null($title)) { $select[] = '`' . $title . '` AS `title`'; } if (!is_null($description)) { $select[] = '`' . $description . '` AS `description`'; } if (!is_null($pubDate)) { $select[] = '`' . $pubDate . '` AS `pubDate`'; } $query->select($select); } }
/** * Affect the One_Query appropriately * * @param One_Query $q */ public function affectQuery(One_Query $q) { $scheme = $q->getScheme(); $name = $this->getName(); $value = $this->getValue(); $op = $this->getOp($this->getCfg('optype')); if (trim($value) != '') { if (!is_null($this->getCfg('role'))) { $parts = explode(':', $this->getCfg('role')); $role = $parts[1]; $link = $scheme->getLink($role); if (!is_null($link)) { $target = $link->getTarget(); $targetScheme = One_Repository::getScheme($target); $targetAttr = $this->getCfg('targetAttribute'); if (!is_null($targetScheme->getAttribute($targetAttr))) { if (!is_null($op)) { $op->affectQuery($q, $role . ':' . $targetAttr, $value); } else { $q->where($role . ':' . $targetAttr, 'contains', $value); // default action } } } } else { if (!is_null($scheme->getAttribute($name))) { if (!is_null($op)) { $op->affectQuery($q, $name, $value); } else { $q->where($name, 'contains', $value); // default action } } } } }
/** * Return the number of results when the query is performed * * @param One_Query $query * @param boolean $overrideFilters * @return int */ public function executeSchemeCount(One_Query $query, $overrideFilters = false) { $scheme = $query->getScheme(); $db = $this->db($scheme); $renderer = $this->getRenderer(); // Need to remember the old Select in case the One_Query is reused afterwards $oldSelect = $query->getSelect(); $query->setSelect(array('COUNT(*) AS total')); $sql = $renderer->render($query, $overrideFilters); // put the old Select back into the One_Query $query->setSelect($oldSelect); // execute query $result = mysql_query($sql, $db); if ($result == false) { throw new One_Exception(mysql_error() . '<br />' . $sql); } $count = mysql_result($result, 0, 'total'); return $count; }
/** * Returns the scheme of the One_Query instance * * @return One_Scheme */ public function getInstanceScheme() { $instanceScheme = $this->query->getScheme(); return $instanceScheme; }
/** * Return the number of results when the query is performed * * @param One_Query $query * @param boolean $overrideFilters * @return int */ public function executeSchemeCount(One_Query $query, $overrideFilters = false) { $scheme = $query->getScheme(); $db = $this->db($scheme); $renderer = $this->getRenderer(); // Need to remember the old Select in case the One_Query is reused afterwards $oldSelect = $query->getSelect(); $query->setSelect(array('COUNT( * ) AS total')); $sql = $renderer->render($query, $overrideFilters); // put the old Select back into the One_Query $query->setSelect($oldSelect); $db->setQuery($sql); return intval($db->loadResult()); }
/** * Processes any possibly given filters and alters the One_Query object accordingly * * @param One_Query $q */ protected function processQueryConditions($q) { $condition = $this->getVariable('query', ''); if ($condition) { $filters = array(); parse_str($this->getVariable('filters', ''), $filters); $c = One_Repository::getFilter($condition, $q->getScheme()->getName(), $filters); $c->affect($q); } }
/** * Return the number of results when the query is performed * * @param One_Query $query * @param boolean $overrideFilters * @return int */ public function executeSchemeCount(One_Query $query, $overrideFilters = false) { $scheme = $query->getScheme(); $db = $this->db($scheme); $renderer = $this->getRenderer(); // Need to remember the old Select in case the One_Query is reused afterwards $oldSelect = $query->getSelect(); $query->setSelect(array('COUNT(*) AS total')); $sql = $renderer->render($query, $overrideFilters); // put the old Select back into the One_Query $query->setSelect($oldSelect); // execute query $result = $db->query($sql); if ($result === false) { throw new One_Exception('MYSQLI error (' . $db->connect_errno . ') ' . $db->connect_error); } $row = $result->fetch_assoc(); $count = $row['total']; return $count; }
/** * Render the query * * @param One_Query $query * @return string */ public function render(One_Query $query, $overrideFilters = false) { $this->query = $query; $this->scheme = $this->query->getScheme(); // if the person wants to perform a raw query, return the raw query if (!is_null($query->getRaw())) { if (One_Config::get('debug.query')) { echo '<pre>'; var_dump($query->getRaw()); echo '</pre>'; } return $query->getRaw(); } $this->query = $query; $this->scheme = $this->query->getScheme(); $resources = $this->scheme->getResources(); // fetch main table to fetch data from $this->mainTable = $resources['table']; $this->defineRole('$self$'); // add possible filters to the query if (!$overrideFilters && isset($resources['filter'])) { $filters = explode(';', $resources['filter']); if (count($filters) > 0) { foreach ($filters as $filterName) { if ($filterName != '') { $filter = One_Repository::getFilter($filterName, $query->getScheme()->getName()); $filter->affect($query); } } } } // TR20100531 No longer needs to be run after the rest since joins are now checked while adding // sselects, order, ... $joins = NULL; $qJoins = $query->getJoins(); if (count($qJoins) > 0) { foreach ($qJoins as $join => $type) { $this->defineRole($join); $query->setRoleAlias($join, $this->aliases[$join]); $joins .= $this->createJoin($query->getRole($join), $type); } } $selects = $this->aliases['$self$'] . '.*'; if (count($query->getSelect()) > 0) { $selects = $this->createSelects($query->getSelect()); } // get where clauses $whereClauses = $query->getWhereClauses(); $where = NULL; if (!is_null($whereClauses)) { $where = $this->whereClauses($whereClauses); } // get having clauses $havingClauses = $query->getHavingClauses(); $having = NULL; if (!is_null($havingClauses)) { $having = $this->whereClauses($havingClauses); } // get order $order = $this->createOrder(); //get grouping $group = $this->createGroup(); // get limit $limit = $this->createLimit(); $sql = 'SELECT ' . $selects . ' FROM ' . $this->mainTable . ' ' . $this->aliases['$self$']; if (!is_null($joins)) { $sql .= $joins; } if (!is_null($where)) { $sql .= ' WHERE ' . $where; } if (!is_null($group)) { $sql .= ' GROUP BY ' . $group; } if (!is_null($having)) { $sql .= ' HAVING ' . $having; } if (!is_null($order)) { $sql .= ' ORDER BY ' . $order; } if (!is_null($limit)) { /* Use the following format to replace MySQL LIMIT for PL/SQL : SELECT * FROM ( SELECT rownum rnum, a.* FROM( SELECT fieldA,fieldB FROM table ORDER BY fieldA ) a WHERE rownum <= START + LIMIT ) WHERE rnum >= START ** or ** SELECT rownum rnum, a.* FROM( SELECT fieldA,fieldB FROM table ORDER BY fieldA ) a WHERE rownum <= LIMIT */ $qLimit = $this->query->getLimit(); if (isset($qLimit['start']) && intval($qLimit['start']) > -1) { $start = intval($qLimit['start']); } else { $start = 0; } if (isset($qLimit['limit']) && intval($qLimit['limit']) > 0) { $limit = intval($qLimit['limit']); } else { $limit = 50; } // @TODO: clean this up // create alias for rownum field $rnfield = $this->createAlias(); $subsel = $this->createAlias(); $sql = "SELECT rownum {$rnfield}, {$subsel}.* FROM ( {$sql} ) {$subsel} WHERE rownum <= {$limit}"; if ($start) { $sql = "SELECT * FROM ( {$sql} ) WHERE {$rnfield} > {$start}"; } } if (One_Config::get('debug.query')) { echo '<pre>'; var_dump($sql); echo '</pre>'; } return $sql; }
/** * Render the query * * @param One_Query $query * @return string */ public function render(One_Query $query, $overrideFilters = false) { $this->query = $query; $this->scheme = $this->query->getScheme(); // if the person wants to perform a raw query, return the raw query if (!is_null($query->getRaw())) { if (One_Config::get('debug.query')) { echo '<pre>'; var_dump($query->getRaw()); echo '</pre>'; } return $query->getRaw(); } $this->query = $query; $this->scheme = $this->query->getScheme(); $resources = $this->scheme->getResources(); // fetch main table to fetch data from $this->mainTable = $resources['table']; $this->defineRole('$self$'); // add possible filters to the query if (!$overrideFilters && isset($resources['filter'])) { $filters = explode(';', $resources['filter']); if (count($filters) > 0) { foreach ($filters as $filterName) { if ($filterName != '') { $filter = One_Repository::getFilter($filterName, $query->getScheme()->getName()); $filter->affect($query); } } } } // TR20100531 No longer needs to be run after the rest since joins are now checked while adding // sselects, order, ... $joins = NULL; $qJoins = $query->getJoins(); if (count($qJoins) > 0) { foreach ($qJoins as $join => $type) { $this->defineRole($join); $query->setRoleAlias($join, $this->aliases[$join]); $joins .= $this->createJoin($query->getRole($join), $type); } } // *** TODO: change the '*' to only the relavant fields defined in the scheme $selects = $this->aliases['$self$'] . '.*'; if (count($query->getSelect()) > 0) { $selects = $this->createSelects($query->getSelect()); } // get where clauses $whereClauses = $query->getWhereClauses(); $where = NULL; if (!is_null($whereClauses)) { $where = $this->whereClauses($whereClauses); } // get having clauses $havingClauses = $query->getHavingClauses(); $having = NULL; if (!is_null($havingClauses)) { $having = $this->whereClauses($havingClauses); } // get order $order = $this->createOrder(); //get grouping $group = $this->createGroup(); // get limit $limit = $this->createLimit(); $sql = 'SELECT ' . $selects . ' FROM `' . $this->mainTable . '` ' . $this->aliases['$self$']; if (!is_null($joins)) { $sql .= $joins; } if (!is_null($where)) { $sql .= ' WHERE ' . $where; } if (!is_null($group)) { $sql .= ' GROUP BY ' . $group; } if (!is_null($having)) { $sql .= ' HAVING ' . $having; } if (!is_null($order)) { $sql .= ' ORDER BY ' . $order; } if (!is_null($limit)) { $sql .= ' LIMIT ' . $limit; } if (One_Config::get('debug.query')) { echo '<pre>'; var_dump($sql); echo '</pre>'; } return $sql; }
/** * Return the number of results when the query is performed * * @param One_Query $query * @param boolean $overrideFilters * @return int */ public function executeSchemeCount(One_Query $query, $overrideFilters = false) { $scheme = $query->getScheme(); $db = $this->db($scheme); $renderer = $this->getRenderer(); $searchQuery = $renderer->render($query, $overrideFilters); $connectionMeta = $scheme->getResources(); $collection = $db->selectCollection($connectionMeta['collection']); try { $count = $collection->count($searchQuery['query']); } catch (Exception $e) { throw new One_Exception('Query failed: ' . $e->getMessage()); } return $count; }
/** * Processes any possibly given filters and alters the One_Query object accordingly. * * The filter option provided by eg. the com_one menu item is a list of filters, comma-separated, to apply. * The extraParameters are passed to the filters * * @param One_Query $query * @param array $filters */ protected function processQueryConditions(One_Query $query) { $filterString = $this->getVariable('filters', ''); if ($filterString) { $filters = explode(',', $filterString); $filterParameters = parse_ini_string($this->getVariable('filterParameters', ',')); foreach ($filters as $filterName) { $filter = One_Repository::getFilter($filterName, $query->getScheme()->getName(), $filterParameters); $filter->affect($query); } } }