PHP Doctrine_Query::where方法代码示例

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