public function testGetSetConditionsParams() { $query = new Query(Query::SELECT); $this->object->setConditions(function (Query $query) { $query->where('status', 1); }); $this->assertEquals([], $query->getWhere()->getParams()); $query->bindCallback($this->object->getConditions(), $this->object); $this->assertEquals([new Expr('status', '=', 1)], $query->getWhere()->getParams()); }
public function preFind(Event $event, Query $query, $finder) { if ($finder === 'first') { return ['custom' => 'data']; } else { if ($finder === 'all') { $query->fields('id', 'username'); } else { if ($finder === 'list') { return false; } } } return true; }
/** * Filter out all soft deleted records from a select query if $filterDeleted is true. * Only apply the filter if the field being filtered on is not part of the original query. * * @param \Titon\Event\Event $event * @param \Titon\Db\Query $query * @param string $finder * @return bool */ public function preFind(Event $event, Query $query, $finder) { $config = $this->allConfig(); if ($config['filterDeleted']) { $where = $query->getWhere(); if ($config['useFlag']) { if (!$where->hasParam($config['flagField'])) { $query->where($config['flagField'], false); } } else { if (!$where->hasParam($config['deleteField'])) { $query->where($config['deleteField'], null); } } } return true; }
/** * Before a save occurs, generate a unique slug using another field as the base. * If no data exists, or the base doesn't exist, or the slug is already set, exit early. * * @param \Titon\Event\Event $event * @param \Titon\Db\Query $query * @param int|int[] $id * @param array $data * @return bool */ public function preSave(Event $event, Query $query, $id, array &$data) { $config = $this->allConfig(); if (empty($data) || empty($data[$config['field']]) || !empty($data[$config['slug']])) { return true; } else { if ($query->getType() === Query::UPDATE && !$config['onUpdate']) { return true; } } $slug = static::slugify($data[$config['field']]); // Leave a gap of 3 to account for the appended numbers if (mb_strlen($slug) > $config['length'] - 3) { $slug = mb_substr($slug, 0, $config['length'] - 3); } if ($config['unique']) { $slug = $this->makeUnique($id, $slug); } $data[$config['slug']] = $slug; return true; }
/** * Resolve the list of values that will be required for PDO statement binding. * * @param \Titon\Db\Query $query * @return array */ public function resolveParams(Query $query) { $params = []; $schema = $query->getRepository()->getSchema()->getColumns(); foreach ($query->getGroupedBindings() as $groupedBinds) { foreach ($groupedBinds as $binds) { $params[] = $this->resolveBind($binds['field'], $binds['value'], $schema); } } foreach ($query->getCompounds() as $compound) { $params = array_merge($params, $this->resolveParams($compound)); } return $params; }
public function testResolveParamsCompoundQueries() { $query1 = new Query(Query::SELECT, $this->table); $query1->where('username', 'like', '%foo%'); $query2 = new Query(Query::SELECT, $this->table); $query2->where('username', 'like', '%bar%'); $query1->union($query2); $this->assertEquals([['%foo%', PDO::PARAM_STR], ['%bar%', PDO::PARAM_STR]], $this->object->resolveParams($query1)); }
/** * Add a new compound query. Only select queries can be used with compounds. * * @param string $type * @param \Titon\Db\Query $query * @return $this * @throws \Titon\Db\Exception\InvalidQueryException */ protected function _addCompound($type, Query $query) { if ($query->getType() !== self::SELECT) { throw new InvalidQueryException(sprintf('Only a select query can be used with %s', $type)); } $query->attribute('compound', $type); $this->_compounds[] = $query; return $this; }
/** * Format the fields values structure depending on the type of query. * * @param \Titon\Db\Query $query * @return string */ public function formatValues(Query $query) { $fields = $query->getData(); switch ($query->getType()) { case Query::INSERT: return sprintf($this->getClause(self::GROUP), implode(', ', array_fill(0, count($fields), '?'))); break; case Query::MULTI_INSERT: $value = sprintf($this->getClause(self::GROUP), implode(', ', array_fill(0, count($fields[0]), '?'))); return implode(', ', array_fill(0, count($fields), $value)); break; } return ''; }
/** * Prepares a node for removal by moving all following nodes up. * * @param int $id * @param int $index */ protected function _removeNode($id, $index) { $pk = $this->getRepository()->getPrimaryKey(); foreach ([$this->getConfig('leftField'), $this->getConfig('rightField')] as $field) { $this->_moveNode(function (Query $query) use($field, $index, $id, $pk) { $query->where($field, '>=', $index); if ($id) { $query->where($pk, '!=', $id); } }, [$field => Query::expr($field, '-', 2)]); } }
public function testUpdateWithExpressions() { $this->loadFixtures('Stats'); $stat = new Stat(); $this->assertEquals(new EntityCollection([new Entity(['id' => 1, 'name' => 'Warrior', 'health' => 1500]), new Entity(['id' => 2, 'name' => 'Ranger', 'health' => 800]), new Entity(['id' => 3, 'name' => 'Mage', 'health' => 600])]), $stat->select('id', 'name', 'health')->orderBy('id', 'asc')->all()); $query = $stat->query(Query::UPDATE); $query->data(['health' => Query::expr('health', '+', 75)]); $this->assertEquals(3, $query->save()); $this->assertEquals(new EntityCollection([new Entity(['id' => 1, 'name' => 'Warrior', 'health' => 1575]), new Entity(['id' => 2, 'name' => 'Ranger', 'health' => 875]), new Entity(['id' => 3, 'name' => 'Mage', 'health' => 675])]), $stat->select('id', 'name', 'health')->orderBy('id', 'asc')->all()); $this->assertEquals(1, $stat->update(2, ['health' => Query::expr('health', '-', 125)])); $this->assertEquals(new EntityCollection([new Entity(['id' => 1, 'name' => 'Warrior', 'health' => 1575]), new Entity(['id' => 2, 'name' => 'Ranger', 'health' => 750]), new Entity(['id' => 3, 'name' => 'Mage', 'health' => 675])]), $stat->select('id', 'name', 'health')->orderBy('id', 'asc')->all()); }
/** * Primary method that handles the processing of update queries. * * Before a save is executed, a `preSave` and `preUpdate` event will be triggered. * This event allows data to be modified before saving via references. * If this event returns a falsey value, the save will exit early and * return a 0. This allows behaviors and events to cease save operations. * * Before the driver is queried, the connection context will be set to `write`. * * After the query has executed, and no rows have been affected, the method * will exit early with a 0 response. Otherwise, a `postSave` and `postUpdate` event will be triggered. * * @param \Titon\Db\Query $query * @param mixed $options { * @type bool $before Will trigger before callbacks * @type bool $after Will trigger after callbacks * } * @return int * - The count of records updated * - 0 if save operation failed */ protected function _processUpdate(Query $query, array $options = []) { $data = $query->getData(); $options = $options + ['before' => true, 'after' => true]; // Fetch ID $this->id = $id = $this->findID($query); if ($options['before']) { foreach (['db.preSave', 'db.preUpdate'] as $event) { $event = $this->emit($event, [$query, $id, &$data]); if (!$event->getState()) { return 0; } } } // Reset the modified data $query->data($data); // Update the connection context and execute the query $count = $this->getDriver()->setContext('write')->executeQuery($query)->save(); // Exit early if save failed if ($count === false) { return false; } if ($options['after']) { $this->emit('db.postSave db.postUpdate', [$id, $count]); } return $count; }
/** * Build the TRUNCATE query. * * @param \Titon\Db\Query $query * @return string */ public function buildTruncate(Query $query) { return $this->renderStatement(Query::TRUNCATE, ['table' => $this->formatTable($query->getTable())] + $this->formatAttributes($query->getAttributes())); }
/** * Append the current timestamp to the data. * * @param \Titon\Event\Event $event * @param \Titon\Db\Query $query * @param int|int[] $id * @param array $data * @return bool */ public function preSave(Event $event, Query $query, $id, array &$data) { $data[$this->getConfig($query->getType() === Query::UPDATE ? 'updateField' : 'createField')] = time(); return true; }
public function testFunc() { $this->assertInstanceOf('Titon\\Db\\Query\\Func', Query::func('SUBSTRING', ['Foo', 1, 2])); }
public function testFormatValues() { $query = new Query(Query::INSERT, new User()); $query->data(['id' => 1, 'username' => 'miles', 'email' => '*****@*****.**']); $this->assertEquals('(?, ?, ?)', $this->object->formatValues($query)); }