protected function getResults(xPDOQuery &$c) { $list = array(); $this->currentIndex = 0; if ($c->prepare()) { if ($c->stmt->execute()) { while ($row = $c->stmt->fetch(PDO::FETCH_ASSOC)) { $object_id = $row['object_id']; if (empty($list[$object_id])) { $list[$object_id] = $row; $list[$object_id]['tvs'] = array(); $this->currentIndex++; } if (!empty($row['tv_name'])) { $list[$object_id]['tvs'][$row['tv_name']] = array('tv_id' => $row['tv_id'], 'caption' => $row['tv_caption'], 'category' => $row['tv_category'], 'value_id' => $row['tv_value_id'], 'value' => $row['tv_value']); } } } else { if ($c->stmt->errorCode() !== "00000") { $this->modx->log(xPDO::LOG_LEVEL_ERROR, __CLASS__); $this->modx->log(xPDO::LOG_LEVEL_ERROR, print_r($c->stmt->errorInfo(), true)); $this->modx->log(xPDO::LOG_LEVEL_ERROR, $c->toSQL()); } } } return $list; }
protected function countTotal($className, xPDOQuery &$query) { if (!$query->prepare()) { return false; } $sql = "SELECT count(*) as count FROM (" . $query->toSQL() . ") as t;"; if (!($stmt = $this->modx->prepare($sql)) or !$stmt->execute()) { return false; } return current((array) $stmt->fetch(PDO::FETCH_NUM)); }
/** * Returns select statement for easy reading * * @access public * @param xPDOQuery $query The query to print * @return string The select statement * @author Coroico <*****@*****.**> */ public function niceQuery(xPDOQuery $query = null) { $searched = array("SELECT", "GROUP_CONCAT", "LEFT JOIN", "INNER JOIN", "EXISTS", "LIMIT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY", "OR", "AND", "IFNULL", "ON", "MATCH", "AGAINST", "COUNT"); $replace = array(" \r\nSELECT", " \r\nGROUP_CONCAT", " \r\nLEFT JOIN", " \r\nINNER JOIN", " \r\nEXISTS", " \r\nLIMIT", " \r\nFROM", " \r\nWHERE", " \r\nGROUP BY", " \r\nHAVING", " ORDER BY", " \r\nOR", " \r\nAND", " \r\nIFNULL", " \r\nON", " \r\nMATCH", " \r\nAGAINST", " \r\nCOUNT"); $output = ''; if (isset($query)) { $query->prepare(); $output = str_replace($searched, $replace, " " . $query->toSQL()); } return $output; }
/** * @param xPDOQuery $c * * @return xPDOQuery */ public function prepareQueryBeforeCount(xPDOQuery $c) { $c->leftJoin('modMediaSource', 'Source'); $c->leftJoin($this->classKey, 'Thumb', "`{$this->classKey}`.`id` = `Thumb`.`parent`"); $c->groupby($this->classKey . '.id'); $c->select('`Source`.`name` as `source_name`'); $c->select('`Thumb`.`url` as `thumbnail`'); $c->where(array('resource_id' => $this->getProperty('resource_id'))); $parent = $this->getProperty('parent'); if ($parent !== false) { $c->where(array('parent' => $parent)); } $query = trim($this->getProperty('query')); if (!empty($query)) { $c->where(array('file:LIKE' => "%{$query}%", 'OR:name:LIKE' => "%{$query}%", 'OR:alt:LIKE' => "%{$query}%", 'OR:description:LIKE' => "%{$query}%", 'OR:add:LIKE' => "%{$query}%")); } $tags = array_map('trim', explode(',', $this->getProperty('tags'))); if (!empty($tags[0])) { $tags = implode("','", $tags); $c->innerJoin('msResourceFileTag', 'Tag', "`{$this->classKey}`.`id` = `Tag`.`file_id` AND `Tag`.`tag` IN ('" . $tags . "')"); $c->groupby($this->classKey . '.id'); $c->prepare(); $this->modx->log(1, $c->toSQL()); } return $c; }
/** * Returns select statement for easy reading. * * @param xPDOQuery $query The query to print * * @return string The select statement * * @author Coroico <*****@*****.**> */ public function niceQuery(xPDOQuery $query = null) { $searched = array('SELECT', 'GROUP_CONCAT', 'LEFT JOIN', 'INNER JOIN', 'EXISTS', 'LIMIT', 'FROM', 'WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'OR', 'AND', 'IFNULL', 'ON', 'MATCH', 'AGAINST', 'COUNT'); $replace = array(" \r\nSELECT", " \r\nGROUP_CONCAT", " \r\nLEFT JOIN", " \r\nINNER JOIN", " \r\nEXISTS", " \r\nLIMIT", " \r\nFROM", " \r\nWHERE", " \r\nGROUP BY", " \r\nHAVING", ' ORDER BY', " \r\nOR", " \r\nAND", " \r\nIFNULL", " \r\nON", " \r\nMATCH", " \r\nAGAINST", " \r\nCOUNT"); $output = ''; if (isset($query)) { $query->prepare(); $output = str_replace($searched, $replace, ' ' . $query->toSQL()); } return $output; }
/** * Main method for query processing and fetching rows * It can return string with SQL query, array or raw rows or processed HTML chunks * * @return array|bool|string */ public function run() { $this->makeQuery(); $this->addTVFilters(); $this->addTVs(); $this->addJoins(); $this->addGrouping(); $this->addSelects(); $this->addWhere(); $this->addSort(); $this->prepareQuery(); $output = ''; if (strtolower($this->config['return']) == 'sql') { $this->addTime('Returning raw sql query'); $output = $this->query->toSQL(); } else { $this->modx->exec('SET SQL_BIG_SELECTS = 1'); $this->addTime('SQL prepared <small>"' . $this->query->toSQL() . '"</small>'); $tstart = microtime(true); if ($this->query->stmt->execute()) { $this->modx->queryTime += microtime(true) - $tstart; $this->modx->executedQueries++; $this->addTime('SQL executed', microtime(true) - $tstart); $this->setTotal(); $rows = $this->query->stmt->fetchAll(PDO::FETCH_ASSOC); $this->addTime('Rows fetched'); $rows = $this->checkPermissions($rows); $this->count = count($rows); if (strtolower($this->config['return']) == 'ids') { $ids = array(); foreach ($rows as $row) { $ids[] = $row[$this->pk]; } $output = implode(',', $ids); } elseif (strtolower($this->config['return']) == 'data') { $rows = $this->prepareRows($rows); $this->addTime('Returning raw data'); $output =& $rows; } elseif (strtolower($this->config['return']) == 'json') { $rows = $this->prepareRows($rows); $this->addTime('Returning raw data as JSON string'); $output = $this->modx->toJSON($rows); } elseif (strtolower($this->config['return']) == 'serialize') { $rows = $this->prepareRows($rows); $this->addTime('Returning raw data as serialized string'); $output = serialize($rows); } else { $rows = $this->prepareRows($rows); $time = microtime(true); $output = array(); foreach ($rows as $row) { if (!empty($this->config['additionalPlaceholders'])) { $row = array_merge($this->config['additionalPlaceholders'], $row); } $row['idx'] = $this->idx++; // Add placeholder [[+link]] if specified if (!empty($this->config['useWeblinkUrl'])) { if (!isset($row['context_key'])) { $row['context_key'] = ''; } if (isset($row['class_key']) && $row['class_key'] == 'modWebLink') { $row['link'] = isset($row['content']) && is_numeric(trim($row['content'], '[]~ ')) ? $this->makeUrl(intval(trim($row['content'], '[]~ ')), $row) : (isset($row['content']) ? $row['content'] : ''); } else { $row['link'] = $this->makeUrl($row['id'], $row); } } else { $row['link'] = ''; } $tpl = $this->defineChunk($row); if (empty($tpl)) { $output[] = '<pre>' . $this->getChunk('', $row) . '</pre>'; } else { $output[] = $this->getChunk($tpl, $row, $this->config['fastMode']); } } $this->addTime('Returning processed chunks', microtime(true) - $time); if (!empty($this->config['toSeparatePlaceholders'])) { $this->modx->setPlaceholders($output, $this->config['toSeparatePlaceholders']); $output = ''; } else { $output = implode($this->config['outputSeparator'], $output); } } } else { $this->modx->log(modX::LOG_LEVEL_INFO, '[pdoTools] ' . $this->query->toSQL()); $errors = $this->query->stmt->errorInfo(); $this->modx->log(modX::LOG_LEVEL_ERROR, '[pdoTools] Error ' . $errors[0] . ': ' . $errors[2]); $this->addTime('Could not process query, error #' . $errors[1] . ': ' . $errors[2]); } } return $output; }