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; }
/** * 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; }); } }
/** * 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; }
/** * @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; }
/** * 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)); }
/** * 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); }
/** * 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; }
/** * 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); }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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); }
/** * 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('='); }
/** * 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; }
/** * {@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]; }
/** * 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(); }
/** * 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; }
/** * 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)]; }
/** * 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 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); }
/** * 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; }
/** * 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; }
/** * 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()); }
/** * 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()); }
/** * 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)); } }
/** * 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')); }
/** * 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()); }
/** * 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; }