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));
}
