PHP Class cake\database\ Query Code Examples

PHP cake\database\Query - 30 examples found. These are the top rated real world PHP examples of class cake\database\Query extracted from open source projects. You can rate examples to help us improve the quality of examples
Inheritance: implements Cake\Database\ExpressionInterface, implements IteratorAggregat\IteratorAggregate, use trait Cake\Database\TypeMapTrait
Example #1
1
 /**
  * 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('=');
 }
Example #9
0
 /**
  * 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);
 }
Example #10
0
 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;
     }
 }
Example #11
0
 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;
 }
Example #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);
 }
Example #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;
 }
Example #14
0
 /**
  * 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;
 }
Example #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;
 }
Example #18
0
 /**
  * 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;
 }
Example #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;
         });
     }
 }
Example #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);
 }
Example #22
0
 /**
  * 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());
 }
Example #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();
 }
Example #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)];
 }
Example #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;
 }
Example #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());
 }