public function execute(&$value, &$error)
{
$className = $this->getParameter('class');
$columnName = $className . '.' . $this->getParameter('column');
$primaryKeys = sfDoctrine::getTable($className)->getPrimaryKeys();
foreach ($primaryKeys as $primaryKey) {
if (is_null($primaryKeyValue = $this->getContext()->getRequest()->getParameter($primaryKey))) {
}
break;
}
$query = new Doctrine_Query();
$query->from($className);
$value = strtolower($value);
if ($primaryKeyValue === null) {
$query->where($columnName . ' = ?');
$res = $query->execute(array($value));
} else {
$query->where($columnName . ' = ? AND ' . $primaryKey . ' != ?');
$res = $query->execute(array($value, $primaryKeyValue));
}
if (sizeof($res)) {
$error = $this->getParameterHolder()->get('unique_error');
return false;
}
return true;
}
public function testApplyInheritance()
{
$query = new Doctrine_Query();
$query->from('InheritanceDeal d, d.Users u');
$query->where('u.id = 1');
$sql = 'SELECT i.id AS i__id, i.name AS i__name, i2.id AS i2__id, i2.username AS i2__username FROM inheritance_deal i LEFT JOIN inheritance_entity_user i3 ON (i.id = i3.entity_id) AND i3.type = 1 LEFT JOIN inheritance_user i2 ON i2.id = i3.user_id WHERE i2.id = 1';
$this->assertEqual($sql, $query->getSql());
}
public static function getTypes($public_only = false)
{
$q = new Doctrine_Query();
$q->select('i.*');
$q->from('GcrInstitutionType i');
if ($public_only) {
$q->where('i.is_public = ?', 't');
}
return $q->execute();
}
public static function getEschoolTypes($public_only = false)
{
$q = new Doctrine_Query();
$q->select('e.*');
$q->from('GcrEschoolType e');
if ($public_only) {
$q->where('e.is_public = ?', 't');
}
return $q->execute();
}
public function testSubqueryExtractionUsesWrongAliases()
{
$q = new Doctrine_Query();
$q->from('RelX x');
$q->leftJoin('x.y xy');
$q->where('x.created_at IN (SELECT MAX(x2.created_at) latestInCategory FROM RelX x2 WHERE x.category = x2.category)');
$q->limit(5);
//echo $sql = $q->getSqlQuery();
// echo $sql;
$xs = $q->execute();
// Doctrine_Ticket_1254_TestCase : method testSubqueryExtractionUsesWrongAliases failed on line 76
// This fails sometimes at
$this->assertEqual(2, count($xs));
}
public function testConditionParser2()
{
$query = new Doctrine_Query($this->connection);
$query->select('User.id')->from("User")->where("User.name LIKE 'z%' OR User.name LIKE 's%'");
$sql = "SELECT e.id AS e__id FROM entity e WHERE (e.name LIKE 'z%' OR e.name LIKE 's%') AND (e.type = 0)";
$this->assertEqual($query->getQuery(), $sql);
$query->where("(User.name LIKE 'z%') OR (User.name LIKE 's%')");
$this->assertEqual($query->getQuery(), $sql);
$query->where("((User.name LIKE 'z%') OR (User.name LIKE 's%'))");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(((User.name LIKE 'z%') OR (User.name LIKE 's%')))");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(((User.name LIKE 'z%') OR User.name LIKE 's%'))");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(User.name LIKE 'z%') OR User.name LIKE 's%' AND User.name LIKE 'a%'");
$sql = "SELECT e.id AS e__id FROM entity e WHERE ((e.name LIKE 'z%' OR e.name LIKE 's%') AND e.name LIKE 'a%') AND (e.type = 0)";
$this->assertEqual($query->getQuery(), "SELECT e.id AS e__id FROM entity e WHERE (e.name LIKE 'z%' OR (e.name LIKE 's%' AND e.name LIKE 'a%')) AND (e.type = 0)");
$query->where("(((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%'");
$this->assertEqual($query->getQuery(), $sql);
$query->where("((((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%')");
$this->assertEqual($query->getQuery(), $sql);
$query->where("(((((User.name LIKE 'z%') OR User.name LIKE 's%')) AND User.name LIKE 'a%'))");
$this->assertEqual($query->getQuery(), $sql);
}
public function testSubqueryExtractionUsesWrongAliases()
{
$q = new Doctrine_Query();
$q->from('RelX x');
$q->leftJoin('x.y xy');
$q->where('x.created_at IN (SELECT MAX(x2.created_at) latestInCategory FROM RelX x2 WHERE x.category = x2.category)');
$q->limit(5);
//echo $sql = $q->getSql();
// echo $sql;
$xs = $q->execute();
$this->assertEqual(3, count($xs));
}
public function testMultipleAggregateValuesWithArrayFetching()
{
$query = new Doctrine_Query();
$query->select('u.*, COUNT(DISTINCT b.id) num_books, COUNT(DISTINCT a.id) num_albums');
$query->from('User u');
$query->leftJoin('u.Album a, u.Book b');
$query->where("u.name = 'jon'");
$query->limit(1);
$users = $query->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
try {
$name = $users[0]['name'];
$num_albums = $users[0]['num_albums'];
$num_books = $users[0]['num_books'];
} catch (Doctrine_Exception $e) {
$this->fail();
}
$this->assertEqual($num_albums, 3);
$this->assertEqual($num_books, 2);
}
public function testBug()
{
$person = $this->newPerson('Fixe');
$profile = $this->newProfile('Work', $person);
$guardUser = $person->get('sfGuardUser');
$id = $guardUser->get('id');
$guardUser->free();
$query = new Doctrine_Query();
$query->select('s.*, p.*, ps.*');
$query->from('sfGuardUser s');
$query->innerJoin('s.Person p');
$query->leftJoin('p.Profiles ps');
$query->where('s.id = ?', $id);
$user = $query->fetchOne();
$array = $user->toArray(true);
$this->assertEqual($array['id'], 1);
$this->assertEqual($array['name'], 'Fixe');
$this->assertTrue(isset($array['Person']['Profiles'][0]));
}
public function remove($domain_key, $owner_uid, $target_uid)
{
if (!$domain_key || !$owner_uid || !$target_uid) {
return false;
}
$q = new Doctrine_Query();
$q->select('id');
$q->from('RingsideFriend');
$q->where("domain_key='{$domain_key}' and from_id='{$owner_uid}' and to_id='{$target_uid}'");
$res = $q->execute();
if (count($res) > 0) {
$res = $res->toArray();
$friend_id = $res[0]['id'];
$row = Doctrine::getTable('RingsideFriend')->find($friend_id);
if ($row !== false) {
return $row->delete();
} else {
return false;
}
}
return false;
}
public function buildIntegrityRelationQuery(Doctrine_Record $record)
{
$q = new Doctrine_Query();
$aliases = array();
$indexes = array();
$root = $record->getTable()->getComponentName();
$rootAlias = strtolower(substr($root, 0, 1));
$aliases[$rootAlias] = $root;
foreach ((array) $record->getTable()->getIdentifier() as $id) {
$field = $rootAlias . '.' . $id;
$cond[] = $field . ' = ?';
$fields[] = $field;
$params = $record->get($id);
}
$fields = implode(', ', $fields);
$components[] = $root;
$this->buildIntegrityRelations($record->getTable(), $aliases, $fields, $indexes, $components);
$q->select($fields)->from($root . ' ' . $rootAlias);
foreach ($aliases as $alias => $name) {
$q->leftJoin($rootAlias . '.' . $name . ' ' . $alias);
}
$q->where(implode(' AND ', $cond));
return $q->execute(array($params));
}
public function testTicket()
{
$q1 = new Doctrine_Query();
$q1->select('p.*');
$q1->from('Testing_Product p');
$q1->where('p.id = ?', $this->p1['id']);
$q1->addSelect('a.*, ad.*');
$q1->leftJoin('p.Attributes a');
$q1->leftJoin('a.Definition ad');
$q2 = new Doctrine_Query();
$q2->select('p.*');
$q2->from('Testing_Product p');
$q2->where('p.id = ?', $this->p2['id']);
$q2->addSelect('a.*, ad.*');
$q2->leftJoin('p.Attributes a');
$q2->leftJoin('a.Definition ad');
// This query works perfect
$r1 = $q1->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//var_dump($r1);
// This query throws an exception!!!
$r2 = $q2->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//$r2 = $q2->execute();
//var_dump($r2);
}
/**
* inserts node as parent of dest record
*
* @return bool
* @todo Wrap in transaction
*/
public function insertAsParentOf(Doctrine_Record $dest)
{
// cannot insert a node that has already has a place within the tree
if ($this->isValidNode()) {
return false;
}
// cannot insert as parent of root
if ($dest->getNode()->isRoot()) {
return false;
}
// cannot insert as parent of itself
if ($dest === $this->record || $dest->exists() && $this->record->exists() && $dest->identifier() === $this->record->identifier()) {
throw new Doctrine_Tree_Exception("Cannot insert node as parent of itself");
return false;
}
$newLeft = $dest->getNode()->getLeftValue();
$newRight = $dest->getNode()->getRightValue() + 2;
$newRoot = $dest->getNode()->getRootValue();
$newLevel = $dest->getNode()->getLevel();
$conn = $this->record->getTable()->getConnection();
try {
$conn->beginInternalTransaction();
// Make space for new node
$this->shiftRLValues($dest->getNode()->getRightValue() + 1, 2, $newRoot);
// Slide child nodes over one and down one to allow new parent to wrap them
$componentName = $this->_tree->getBaseComponent();
$q = new Doctrine_Query();
$q->update($componentName);
$q->set("{$componentName}.lft", "{$componentName}.lft + 1");
$q->set("{$componentName}.rgt", "{$componentName}.rgt + 1");
$q->set("{$componentName}.level", "{$componentName}.level + 1");
$q->where("{$componentName}.lft >= ? AND {$componentName}.rgt <= ?", array($newLeft, $newRight));
$q = $this->_tree->returnQueryWithRootId($q, $newRoot);
$q->execute();
$this->record['level'] = $newLevel;
$this->insertNode($newLeft, $newRight, $newRoot);
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
throw $e;
}
return true;
}
public function addTagsListColumnQuery(Doctrine_Query $query, $field, $values)
{
if (!is_array($values)) {
$values = array($values);
} else {
$values = array_keys($values);
}
if (!count($values)) {
return;
}
$ids = Doctrine::getTable('tagging')->createQuery()->select('taggable_id')->leftJoin('tagging.Tag tag')->where('taggable_model = ?', $this->getModelName())->andWhereIn('tag.name', $values)->groupBy('taggable_id')->execute(array(), Doctrine::HYDRATE_SCALAR);
$ids = array_map(create_function('$i', 'return $i["tagging_taggable_id"];'), $ids);
if (empty($ids)) {
$query->where('false');
} else {
$query->whereIn($query->getRootAlias() . '.id', $ids);
}
}
/**
* inserts node as parent of dest record
*
* @return bool
* @todo Wrap in transaction
*/
public function insertAsParentOf(Doctrine_Record $dest)
{
// cannot insert a node that has already has a place within the tree
if ($this->isValidNode()) {
return false;
}
// cannot insert as parent of root
if ($dest->getNode()->isRoot()) {
return false;
}
$newLeft = $dest->getNode()->getLeftValue();
$newRight = $dest->getNode()->getRightValue() + 2;
$newRoot = $dest->getNode()->getRootValue();
$newLevel = $dest->getNode()->getLevel();
// Make space for new node
$this->shiftRLValues($dest->getNode()->getRightValue() + 1, 2, $newRoot);
// Slide child nodes over one and down one to allow new parent to wrap them
$componentName = $this->_tree->getBaseComponent();
$q = new Doctrine_Query();
$q->update($componentName);
$q->set("{$componentName}.lft", "{$componentName}.lft + 1");
$q->set("{$componentName}.rgt", "{$componentName}.rgt + 1");
$q->set("{$componentName}.level", "{$componentName}.level + 1");
$q->where("{$componentName}.lft >= ? AND {$componentName}.rgt <= ?", array($newLeft, $newRight));
$q = $this->_tree->returnQueryWithRootId($q, $newRoot);
$q->execute();
$this->record['level'] = $newLevel;
$this->insertNode($newLeft, $newRight, $newRoot);
return true;
}
public function testCountMaintainsParams()
{
$q = new Doctrine_Query();
$q->from('User u');
$q->leftJoin('u.Phonenumber p');
$q->where('u.name = ?', 'zYne');
$this->assertEqual($q->count(), $q->execute()->count());
}
public function testLimitSubqueryNotNeededIfSelectSingleFieldDistinct()
{
$q = new Doctrine_Query();
$q->select('u.id')->distinct()->from('User u LEFT JOIN u.Phonenumber p');
$q->where('u.name = ?');
$q->limit(5);
$users = $q->execute(array('zYne'));
$this->assertEqual(1, $users->count());
$this->assertEqual($q->getSql(), "SELECT DISTINCT e.id AS e__id FROM entity e LEFT JOIN phonenumber p ON e.id = p.entity_id WHERE e.name = ? AND (e.type = 0) LIMIT 5");
}
/**
*
* @param type $operationalCountryId
* @return Doctrine_Query
*/
protected function addOperationalCountryFilter(Doctrine_Query $query, $operationalCountryId)
{
if (is_null($operationalCountryId)) {
$query->addWhere('id = ?', WorkWeek::DEFAULT_WORK_WEEK_ID);
} else {
$query->where('operational_country_id = ?', $operationalCountryId);
}
return $query;
}
public function deleteApp($appId)
{
$keyService = Api_ServiceFactory::create('KeyService');
$keyService->deleteAllKeysets($appId);
$q = new Doctrine_Query();
$q->select('*');
$q->from('RingsideApp');
$q->where("id={$appId}");
$res = $q->execute();
if (count($res) > 0) {
if (!$res[0]->delete()) {
throw new Exception("[AppServiceImpl] could not delete app with id={$appId}");
}
}
}
/**
* Load all relationships or the named relationship passed
*
* @param mixed $name
* @return boolean
*/
public function loadRelated($name = null)
{
$list = array();
$query = new Doctrine_Query($this->_table->getConnection());
if (!isset($name)) {
foreach ($this->data as $record) {
$value = $record->getIncremented();
if ($value !== null) {
$list[] = $value;
}
}
$query->from($this->_table->getComponentName());
$query->where($this->_table->getComponentName() . '.id IN (' . substr(str_repeat("?, ", count($list)), 0, -2) . ')');
return $query;
}
$rel = $this->_table->getRelation($name);
if ($rel instanceof Doctrine_Relation_LocalKey || $rel instanceof Doctrine_Relation_ForeignKey) {
foreach ($this->data as $record) {
$list[] = $record[$rel->getLocal()];
}
} else {
foreach ($this->data as $record) {
$value = $record->getIncremented();
if ($value !== null) {
$list[] = $value;
}
}
}
$dql = $rel->getRelationDql(count($list), 'collection');
$coll = $query->query($dql, $list);
$this->populateRelated($name, $coll);
}
public function testDirectMultipleParameterSetting2()
{
$q = new Doctrine_Query();
$q->from('User')->where('User.id IN (?, ?)', array(1, 2));
$this->assertEqual($q->getQuery(), 'SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE e.id IN (?, ?) AND (e.type = 0)');
$users = $q->execute();
$this->assertEqual($users->count(), 2);
$this->assertEqual($users[0]->name, 'someone');
$this->assertEqual($users[1]->name, 'someone.2');
// the parameters and where part should be reseted
$q->where('User.id IN (?, ?)', array(1, 2));
$users = $q->execute();
$this->assertEqual($users->count(), 2);
$this->assertEqual($users[0]->name, 'someone');
$this->assertEqual($users[1]->name, 'someone.2');
}
public function getAllKeysetsByDomainId($domainId)
{
$q = new Doctrine_Query();
$q->select('*');
$q->from('RingsideKeyring');
$q->where("domain_id='{$domainId}' AND entity_id!='{$domainId}'");
$res = $q->execute();
if (count($res) > 0) {
return $res->toArray();
}
return null;
}
protected function setMenuItemContstraints(Doctrine_Query $q)
{
$q->where('s.level > 1')->andWhere('s.lang = ?', $this->getRoute()->getObject()->getLang())->andWhere('s.level <= ?', $this->getCurrentMenuLevel())->orderBy('s.lft asc')->setHydrationMode(Doctrine::HYDRATE_RECORD_HIERARCHY);
return $q;
}
protected function addCinemaColumnQuery(Doctrine_Query $query, $field, $values)
{
if ($values && $values != 'empty') {
$query->where('r.Exemplars.cinema = ?', "{$values}");
}
}
public function where($where, $params = array())
{
$fixed = self::_fixWhere($where, $params);
return parent::where($fixed['where'], $fixed['params']);
}
