PHP xPDOQuery::toSQL方法代码示例

 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 <coroico@wangba.fr>
  */
 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 <coroico@wangba.fr>
  */
 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;
 }