Inheritance: implements Cake\Database\ExpressionInterface, implements IteratorAggregat\IteratorAggregate, use trait Cake\Database\TypeMapTrait
Beispiel #1
2
 /**
  * Test that cloning goes deep.
  *
  * @return void
  */
 public function testDeepClone()
 {
     $query = new Query($this->connection);
     $query->select(['id', 'title' => $query->func()->concat(['title' => 'literal', 'test'])])->from('articles')->where(['Articles.id' => 1])->offset(10)->limit(1)->order(['Articles.id' => 'DESC']);
     $dupe = clone $query;
     $this->assertEquals($query->clause('where'), $dupe->clause('where'));
     $this->assertNotSame($query->clause('where'), $dupe->clause('where'));
     $dupe->where(['Articles.title' => 'thinger']);
     $this->assertNotEquals($query->clause('where'), $dupe->clause('where'));
     $this->assertNotSame($query->clause('select')['title'], $dupe->clause('select')['title']);
     $this->assertEquals($query->clause('order'), $dupe->clause('order'));
     $this->assertNotSame($query->clause('order'), $dupe->clause('order'));
     $query->order(['Articles.title' => 'ASC']);
     $this->assertNotEquals($query->clause('order'), $dupe->clause('order'));
 }
 /**
  * Generates the LIMIT part of a SQL query
  *
  * @param int $limit the limit clause
  * @param \Cake\Database\Query $query The query that is being compiled
  * @return string
  */
 protected function _buildLimitPart($limit, $query)
 {
     if ($limit === null || $query->clause('offset') === null) {
         return '';
     }
     return sprintf(' FETCH FIRST %d ROWS ONLY', $limit);
 }
 /**
  * Builds the SQL fragment for INSERT INTO.
  *
  * @param array $parts The insert parts.
  * @param \Cake\Database\Query $query The query that is being compiled
  * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions
  * @return string SQL fragment.
  */
 protected function _buildInsertPart($parts, $query, $generator)
 {
     $driver = $query->connection()->driver();
     $table = $driver->quoteIfAutoQuote($parts[0]);
     $columns = $this->_stringifyExpressions($parts[1], $generator);
     return sprintf('INSERT INTO %s (%s)', $table, implode(', ', $columns));
 }
 /**
  * @param array $parts
  * @param \Cake\Database\Query $query
  * @param \Cake\Database\ValueBinder $generator
  * @return string
  */
 protected function _buildSelectPart($parts, $query, $generator)
 {
     $driver = $query->connection()->driver();
     $select = 'SELECT %s%s%s';
     if ($this->_orderedUnion && $query->clause('union')) {
         $select = '(SELECT %s%s%s';
     }
     $distinct = $query->clause('distinct');
     $modifiers = $query->clause('modifier') ?: null;
     $normalized = [];
     $parts = $this->_stringifyExpressions($parts, $generator);
     foreach ($parts as $k => $p) {
         if (!is_numeric($k)) {
             $p = $p . ' AS "' . $k . '"';
         }
         $normalized[] = $p;
     }
     if ($distinct === true) {
         $distinct = 'DISTINCT ';
     }
     if (is_array($distinct)) {
         $distinct = $this->_stringifyExpressions($distinct, $generator);
         $distinct = sprintf('DISTINCT ON (%s) ', implode(', ', $distinct));
     }
     if ($modifiers !== null) {
         $modifiers = $this->_stringifyExpressions($modifiers, $generator);
         $modifiers = implode(' ', $modifiers) . ' ';
     }
     return sprintf($select, $distinct, $modifiers, implode(', ', $normalized));
 }
 /**
  * Transforms an insert query that is meant to insert multiple rows at a time,
  * otherwise it leaves the query untouched.
  *
  * The way Firebird works with multi insert is by having multiple select statements
  * joined with UNION.
  *
  * @param \Cake\Database\Query $query The query to translate
  * @return \Cake\Database\Query
  */
 protected function _insertQueryTranslator($query)
 {
     $v = $query->clause('values');
     if (count($v->values()) === 1 || $v->query()) {
         return $query;
     }
     $newQuery = $query->connection()->newQuery();
     $cols = $v->columns();
     $placeholder = 0;
     $replaceQuery = false;
     foreach ($v->values() as $k => $val) {
         $fillLength = count($cols) - count($val);
         if ($fillLength > 0) {
             $val = array_merge($val, array_fill(0, $fillLength, null));
         }
         foreach ($val as $col => $attr) {
             if (!$attr instanceof ExpressionInterface) {
                 $val[$col] = sprintf(':c%d', $placeholder);
                 $placeholder++;
             }
         }
         $select = array_combine($cols, $val);
         if ($k === 0) {
             $replaceQuery = true;
             $newQuery->select($select);
             continue;
         }
         $q = $newQuery->connection()->newQuery();
         $newQuery->unionAll($q->select($select));
     }
     if ($replaceQuery) {
         $v->query($newQuery);
     }
     return $query;
 }
 /**
  * Returns the SQL representation of the provided query after generating
  * the placeholders for the bound values using the provided generator
  *
  * @param \Cake\Database\Query $query The query that is being compiled
  * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions
  * @return \Closure
  */
 public function compile($query, $generator)
 {
     $sql = '';
     $type = $query->type();
     $query->traverse($this->_sqlCompiler($sql, $query, $generator), $this->{'_' . $type . 'Parts'});
     return $sql;
 }
 /**
  * Modifies the original insert query to append a "RETURNING *" epilogue
  * so that the latest insert id can be retrieved
  *
  * @param \Cake\Database\Query $query The query to translate.
  * @return \Cake\Database\Query
  */
 protected function _insertQueryTranslator($query)
 {
     if (!$query->clause('epilog')) {
         $query->epilog('RETURNING *');
     }
     return $query;
 }
 /**
  * Receives a TupleExpression and changes it so that it conforms to this
  * SQL dialect.
  *
  * It transforms expressions looking like '(a, b) IN ((c, d), (e, f)' into an
  * equivalent expression of the form '((a = c) AND (b = d)) OR ((a = e) AND (b = f))'.
  *
  * It can also transform transform expressions where the right hand side is a query
  * selecting the same amount of columns as the elements in the left hand side of
  * the expression:
  *
  * (a, b) IN (SELECT c, d FROM a_table) is transformed into
  *
  * 1 = (SELECT 1 FROM a_table WHERE (a = c) AND (b = d))
  *
  * @param \Cake\Database\Expression\TupleComparison $expression The expression to transform
  * @param \Cake\Database\Query $query The query to update.
  * @return void
  */
 protected function _transformTupleComparison(TupleComparison $expression, $query)
 {
     $fields = $expression->getField();
     if (!is_array($fields)) {
         return;
     }
     $value = $expression->getValue();
     $op = $expression->getOperator();
     $true = new QueryExpression('1');
     if ($value instanceof Query) {
         $selected = array_values($value->clause('select'));
         foreach ($fields as $i => $field) {
             $value->andWhere([$field . " {$op}" => new IdentifierExpression($selected[$i])]);
         }
         $value->select($true, true);
         $expression->setField($true);
         $expression->setOperator('=');
         return;
     }
     $surrogate = $query->connection()->newQuery()->select($true);
     if (!is_array(current($value))) {
         $value = [$value];
     }
     foreach ($value as $tuple) {
         $surrogate->orWhere(function ($exp) use($fields, $tuple) {
             foreach (array_values($tuple) as $i => $value) {
                 $exp->add([$fields[$i] => $value]);
             }
             return $exp;
         });
     }
     $expression->setField($true);
     $expression->setValue($surrogate);
     $expression->setOperator('=');
 }
 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to turn into a prepared statement.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     return new PDOStatement($statement, $this);
 }
 public function findProductId(Query $query, array $options)
 {
     $result = $query->select(['id'])->where(['article_uid' => $options['uid']])->first();
     if ($result == null) {
         return null;
     } else {
         return $result->id;
     }
 }
 public function findCursor(Query $query)
 {
     $current = $this->request->query('cursor');
     $limit = $this->request->query('limit') ?: 10;
     if ($current) {
         $query->where(['id >' => $current]);
     }
     $query->limit($limit);
     return $query;
 }
Beispiel #12
0
 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $options = [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL];
     $isObject = $query instanceof Query;
     if ($isObject && $query->bufferResults() === false) {
         $options = [];
     }
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
     return new SqlserverStatement($statement, $this);
 }
Beispiel #13
0
 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     $result = new SqliteStatement(new PDOStatement($statement, $this), $this);
     if ($isObject && $query->bufferResults() === false) {
         $result->bufferResults(false);
     }
     return $result;
 }
 /**
  * Iterates over each of the clauses in a query looking for identifiers and
  * quotes them
  *
  * @param \Cake\Database\Query $query The query to have its identifiers quoted
  * @return \Cake\Database\Query
  */
 public function quote(Query $query)
 {
     $binder = $query->valueBinder();
     $query->valueBinder(false);
     if ($query->type() === 'insert') {
         $this->_quoteInsert($query);
     } else {
         $this->_quoteParts($query);
     }
     $query->traverseExpressions([$this, 'quoteExpression']);
     $query->valueBinder($binder);
     return $query;
 }
 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $options = [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY];
     $isObject = $query instanceof Query;
     if ($isObject && $query->bufferResults() === false) {
         $options = [];
     }
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
     $result = new CustomSqlserverStatement(new SqlserverStatement($statement, $this), $this);
     if ($isObject && $query->bufferResults() === false) {
         $result->bufferResults(false);
     }
     return $result;
 }
Beispiel #16
0
 /**
  * Returns the SQL representation of the provided query after generating
  * the placeholders for the bound values using the provided generator
  *
  * @param \Cake\Database\Query $query The query that is being compiled
  * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions
  * @return \Closure
  */
 public function compile(Query $query, ValueBinder $generator)
 {
     $sql = '';
     $type = $query->type();
     $query->traverse($this->_sqlCompiler($sql, $query, $generator), $this->{'_' . $type . 'Parts'});
     // Propagate bound parameters from sub-queries if the
     // placeholders can be found in the SQL statement.
     if ($query->valueBinder() !== $generator) {
         foreach ($query->valueBinder()->bindings() as $binding) {
             $placeholder = ':' . $binding['placeholder'];
             if (preg_match('/' . $placeholder . '(?:\\W|$)/', $sql) > 0) {
                 $generator->bind($placeholder, $binding['value'], $binding['type']);
             }
         }
     }
     return $sql;
 }
 /**
  * Modify the limit/offset to TSQL
  *
  * @param Cake\Database\Query $query The query to translate
  * @return Cake\Database\Query The modified query
  */
 protected function _selectQueryTranslator($query)
 {
     $limit = $query->clause('limit');
     $offset = $query->clause('offset');
     if ($limit && $offset === null) {
         $query->modifier(['_auto_top_' => sprintf('TOP %d', $limit)]);
     }
     if ($offset !== null && !$query->clause('order')) {
         $query->order($query->newExpr()->add('SELECT NULL'));
     }
     if ($this->_version() < 11 && $offset !== null) {
         return $this->_pagingSubquery($query, $limit, $offset);
     }
     return $query;
 }
 /**
  * Returns the passed query after rewriting the DISTINCT clause, so that drivers
  * that do not support the "ON" part can provide the actual way it should be done
  *
  * @param \Cake\Database\Query $original The query to be transformed
  * @return \Cake\Database\Query
  */
 protected function _transformDistinct($original)
 {
     if (!is_array($original->clause('distinct'))) {
         return $original;
     }
     $query = clone $original;
     $distinct = $query->clause('distinct');
     $query->distinct(false);
     $order = new OrderByExpression($distinct);
     $query->select(function ($q) use($distinct, $order) {
         $over = $q->newExpr('ROW_NUMBER() OVER')->add('(PARTITION BY')->add($q->newExpr()->add($distinct)->tieWith(','))->add($order)->add(')')->tieWith(' ');
         return ['_cake_distinct_pivot_' => $over];
     })->limit(null)->offset(null)->order([], true);
     $outer = new Query($query->connection());
     $outer->select('*')->from(['_cake_distinct_' => $query])->where(['_cake_distinct_pivot_' => 1]);
     // Decorate the original query as that is what the
     // end developer will be calling execute() on originally.
     $original->decorateResults(function ($row) {
         if (isset($row['_cake_distinct_pivot_'])) {
             unset($row['_cake_distinct_pivot_']);
         }
         return $row;
     });
     return $outer;
 }
Beispiel #19
0
 /**
  * Conditionally adds a condition to the passed Query that will make it find
  * records where there is no match with this association.
  *
  * @param \Cake\Database\Query $query The query to modify
  * @param array $options Options array containing the `negateMatch` key.
  * @return void
  */
 protected function _appendNotMatching($query, $options)
 {
     $target = $this->_targetTable;
     if (!empty($options['negateMatch'])) {
         $primaryKey = $query->aliasFields((array) $target->primaryKey(), $this->_name);
         $query->andWhere(function ($exp) use($primaryKey) {
             array_map([$exp, 'isNull'], $primaryKey);
             return $exp;
         });
     }
 }
Beispiel #20
0
 /**
  * Executes the provided query after compiling it for the specific dirver
  * dialect and returns the executed Statement object.
  *
  * @param \Cake\Database\Query $query The query to be executed
  * @return \Cake\Database\StatementInterface executed statement
  */
 public function run(Query $query)
 {
     $binder = $query->valueBinder();
     $binder->resetCount();
     list($query, $sql) = $this->driver()->compileQuery($query, $binder);
     $statement = $this->prepare($sql);
     $binder->attachTo($statement);
     $statement->execute();
     return $statement;
 }
 /**
  * Quotes the table name and columns for an insert query
  *
  * @param Query $query
  * @return void
  */
 protected function _quoteInsert($query)
 {
     list($table, $columns) = $query->clause('insert');
     $table = $this->_driver->quoteIdentifier($table);
     foreach ($columns as &$column) {
         if (is_string($column)) {
             $column = $this->_driver->quoteIdentifier($column);
         }
     }
     $query->insert($columns)->into($table);
 }
 /**
  * Test select with limit on lte SQLServer2008
  *
  * @return void
  */
 public function testSelectLimitOldServer()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection', '_version'], [['dsn' => 'foo']]);
     $driver->expects($this->any())->method('_version')->will($this->returnValue(8));
     $connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
     $connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->limit(10);
     $expected = 'SELECT TOP 10 id, title FROM articles';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->offset(10);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] ' . 'FROM articles) AS _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > :c0';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->order(['id'])->offset(10);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles) AS _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > :c0';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->order(['id'])->where(['title' => 'Something'])->limit(10)->offset(50);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles WHERE title = :c0) AS _cake_paging_ ' . 'WHERE (_cake_paging_._cake_page_rownum_ > :c1 AND _cake_paging_._cake_page_rownum_ <= :c2)';
     $this->assertEquals($expected, $query->sql());
 }
 /**
  * Testing counter cache with lambda returning subqueryn
  *
  * @return void
  */
 public function testLambdaSubquery()
 {
     $this->post->belongsTo('Users');
     $this->post->addBehavior('CounterCache', ['Users' => ['posts_published' => function (Event $event, Entity $entity, Table $table) {
         $query = new Query($this->connection);
         return $query->select(4);
     }]]);
     $before = $this->_getUser();
     $entity = $this->_getEntity();
     $this->post->save($entity);
     $after = $this->_getUser();
     $this->assertEquals(1, $before->get('posts_published'));
     $this->assertEquals(4, $after->get('posts_published'));
 }
 /**
  * Quotes the table name for an update query
  *
  * @param \Cake\Database\Query $query The update query to quote.
  * @return void
  */
 protected function _quoteUpdate($query)
 {
     $table = $query->clause('update')[0];
     if (is_string($table)) {
         $query->update($this->_driver->quoteIdentifier($table));
     }
 }
 /**
  * {@inheritDoc}
  */
 public function __debugInfo()
 {
     $eagerLoader = $this->eagerLoader();
     return parent::__debugInfo() + ['hydrate' => $this->_hydrate, 'buffered' => $this->_useBufferedResults, 'formatters' => count($this->_formatters), 'mapReducers' => count($this->_mapReduce), 'contain' => $eagerLoader ? $eagerLoader->contain() : [], 'matching' => $eagerLoader ? $eagerLoader->matching() : [], 'extraOptions' => $this->_options, 'repository' => $this->_repository];
 }
 /**
  * Test that insert queries have results available to them.
  *
  * @return void
  */
 public function testInsertUsesOutput()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection'], [[]]);
     $connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
     $connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
     $query = new \Cake\Database\Query($connection);
     $query->insert(['title'])->into('articles')->values(['title' => 'A new article']);
     $expected = 'INSERT INTO articles (title) OUTPUT INSERTED.* VALUES (:c0)';
     $this->assertEquals($expected, $query->sql());
 }
Beispiel #27
0
 /**
  * Shows that bufferResults(false) will prevent client-side results buffering
  *
  * @return void
  */
 public function testUnbufferedQuery()
 {
     $query = new Query($this->connection);
     $result = $query->select(['body', 'author_id'])->from('articles')->bufferResults(false)->execute();
     if (!method_exists($result, 'bufferResults')) {
         $result->closeCursor();
         $this->skipIf(true, 'This driver does not support unbuffered queries');
     }
     $this->assertCount(0, $result);
     $list = $result->fetchAll('assoc');
     $this->assertCount(3, $list);
     $result->closeCursor();
     $query = new Query($this->connection);
     $result = $query->select(['body', 'author_id'])->from('articles')->execute();
     $this->assertCount(3, $result);
     $list = $result->fetchAll('assoc');
     $this->assertCount(3, $list);
     $result->closeCursor();
 }
Beispiel #28
0
 /**
  * Transforms the passed query to this Driver's dialect and returns an instance
  * of the transformed query and the full compiled SQL string
  *
  * @param \Cake\Database\Query $query The query to compile.
  * @param \Cake\Database\ValueBinder $generator The value binder to use.
  * @return array containing 2 entries. The first entity is the transformed query
  * and the second one the compiled SQL
  */
 public function compileQuery(Query $query, ValueBinder $generator)
 {
     $processor = $this->newCompiler();
     $translator = $this->queryTranslator($query->type());
     $query = $translator($query);
     return [$query, $processor->compile($query, $generator)];
 }
Beispiel #29
0
 /**
  * Apply translation steps to delete queries.
  *
  * Chops out aliases on delete query conditions as most database dialects do not
  * support aliases in delete queries. This also removes aliases
  * in table names as they frequently don't work either.
  *
  * We are intentionally not supporting deletes with joins as they have even poorer support.
  *
  * @param \Cake\Database\Query $query The query to translate
  * @return \Cake\Database\Query The modified query
  */
 protected function _deleteQueryTranslator($query)
 {
     $hadAlias = false;
     $tables = [];
     foreach ($query->clause('from') as $alias => $table) {
         if (is_string($alias)) {
             $hadAlias = true;
         }
         $tables[] = $table;
     }
     if ($hadAlias) {
         $query->from($tables, true);
     }
     if (!$hadAlias) {
         return $query;
     }
     $conditions = $query->clause('where');
     if ($conditions) {
         $conditions->traverse(function ($condition) {
             if (!$condition instanceof Comparison) {
                 return $condition;
             }
             $field = $condition->getField();
             if ($field instanceof ExpressionInterface || strpos($field, '.') === false) {
                 return $condition;
             }
             list(, $field) = explode('.', $field);
             $condition->setField($field);
             return $condition;
         });
     }
     return $query;
 }
Beispiel #30
-1
 /**
  * Test removeJoin().
  *
  * @return void
  */
 public function testRemoveJoin()
 {
     $query = new Query($this->connection);
     $query->select(['id', 'title'])->from('articles')->join(['authors' => ['type' => 'INNER', 'conditions' => ['articles.author_id = authors.id']]]);
     $this->assertArrayHasKey('authors', $query->join());
     $this->assertSame($query, $query->removeJoin('authors'));
     $this->assertArrayNotHasKey('authors', $query->join());
 }