PHP SelectQuery::setWhere方法代码示例

 /**
  * @return array
  * @param string $type The Schema type to look for.
  * Returns an array of DMRecord IDs that are of the Schema type $type.
  */
 function getRecordIDsByType($type)
 {
     // we're going to get all the IDs that match a given type.
     $query = new SelectQuery();
     $query->addTable("dm_record");
     $query->addTable("dm_schema", INNER_JOIN, "dm_schema.id=dm_record.fk_schema");
     $query->addColumn("id", "", "dm_record");
     $query->setWhere("dm_schema.id='" . addslashes($type) . "'");
     $dbHandler = Services::getService("DatabaseManager");
     $result = $dbHandler->query($query, DATAMANAGER_DBID);
     if (!$result) {
         throwError(new UnknownDBError("RecordManager"));
         return false;
     }
     $array = array();
     while ($result->hasMoreRows()) {
         $array[] = $result->field(0);
         $result->advanceRow();
     }
     $result->free();
     return $array;
 }

 /**
  * Loads the definition data from the database, if not already done.
  * @return bool FALSE on error.
  */
 function load()
 {
     // load our fields from the database
     if ($this->loaded()) {
         //			throwError( new Error("Already loaded from the database for type ".HarmoniType::typeToString($this->_type).".","DataSetTypeDefinition",true));
         return true;
     }
     // attempt to get our ID from the SchemaManager
     if (!$this->_isCreatedByManager) {
         throwError(new Error("The Schema object of type '" . HarmoniType::typeToString($this->_type) . "'\n\t\t\t\twas not meant to interface with the database.", "DataManager", true));
         return false;
     }
     $query = new SelectQuery();
     $query->addTable("dm_schema_field");
     $query->addColumn("id", "", "dm_schema_field");
     $query->addColumn("name", "", "dm_schema_field");
     $query->addColumn("mult", "", "dm_schema_field");
     $query->addColumn("required", "", "dm_schema_field");
     $query->addColumn("active", "", "dm_schema_field");
     $query->addColumn("fieldtype", "", "dm_schema_field");
     $query->addColumn("description", "", "dm_schema_field");
     $query->setWhere("fk_schema='" . addslashes($this->_id) . "'");
     $dbHandler = Services::getService("DatabaseManager");
     $result = $dbHandler->query($query, DATAMANAGER_DBID);
     if (!$result) {
         throwError(new UnknownDBError("DataManager"));
     }
     $rows = array();
     while ($result->hasMoreRows()) {
         $rows[] = $result->getCurrentRow();
         $result->advanceRow();
     }
     $result->free();
     $this->populate($rows);
     return true;
 }

 function test_All_Queries()
 {
     $value = "'Depeche Mode rocks!'";
     $this->dbhandler->connect();
     // create a new queue of queries to execuete
     $queryQueue = new Queue();
     $query = new InsertQuery();
     $query->setTable("test1");
     $query->setColumns(array("value"));
     $query->addRowOfValues(array($value));
     $queryQueue->add($query);
     $query = new InsertQuery();
     $query->setTable("test1");
     $query->setColumns(array(id, value));
     $query->addRowOfValues(array("3000000", $value));
     $queryQueue->add($query);
     $query = new DeleteQuery();
     $query->setTable("test1");
     $query->setWhere("id = 3000000");
     $queryQueue->add($query);
     $query = new UpdateQuery();
     $query->setTable("test1");
     $query->setColumns(array("value"));
     $query->setValues(array($value));
     $query->setWhere("id > 1000 AND id < 1006");
     $queryQueue->add($query);
     $resultQueue = $this->dbhandler->queryQueue($queryQueue);
     $this->assertEqual($this->dbhandler->getTotalNumberOfQueries(), 4);
     $this->assertEqual($this->dbhandler->getTotalNumberOfSuccessfulQueries(), 4);
     $this->assertEqual($this->dbhandler->getTotalNumberOfFailedQueries(), 0);
     $result = $resultQueue->next();
     $this->assertEqual($result->getNumberOfRows(), 1);
     $this->assertNotNull($result->getLastAutoIncrementValue());
     $id = $result->getLastAutoIncrementValue();
     $result = $resultQueue->next();
     $this->assertEqual($result->getNumberOfRows(), 1);
     $this->assertNotNull($result->getLastAutoIncrementValue());
     $result = $resultQueue->next();
     $this->assertEqual($result->getNumberOfRows(), 1);
     $result = $resultQueue->next();
     $query = new SelectQuery();
     $query->setColumns(array("value"));
     $query->addTable("test1");
     $query->setWhere("id = {$id}");
     $result = $this->dbhandler->query($query);
     $this->assertEqual($this->dbhandler->getTotalNumberOfQueries(), 5);
     $this->assertEqual($this->dbhandler->getTotalNumberOfSuccessfulQueries(), 5);
     $this->assertEqual($this->dbhandler->getTotalNumberOfFailedQueries(), 0);
     $this->assertEqual("'" . $result->field("value") . "'", $value);
     $result->free();
 }

 /**
  * testing nested subquery (query, which uses subquery, which uses subquery) and checking, that conditions are correct (for use in prepared queries)
  */
 public function testSubqueryWithConditions()
 {
     $field1 = new Field('id', 0);
     $subq2 = new SelectQuery(array('bans'));
     $subq2->setSelect(array($field1));
     $subq2->setWhere(new Condition('=', new Field('abc'), 10));
     $subq = new SelectQuery(array('users'));
     $subq->setSelect(array($field1));
     $subq->setWhere(new AndOp(new Condition('>', new Field('age'), 14), new Condition('in', new Field('id'), $subq2), new Condition('=', new Field('regdt'), '2009-01-01')));
     $q = new SelectQuery(array('posts'));
     $q->setWhere(new AndOp(new Condition('>', new Field('comment'), 1), new Condition('in', new Field('user_id'), $subq), new Condition('=', new Field('funny'), 1)));
     $this->assertEquals('SELECT `t0`.* FROM `posts` AS `t0` WHERE (`t0`.`comment` > :p1 AND `t0`.`user_id` IN (SELECT `t0`.`id` FROM `users` AS `t0` WHERE (`t0`.`age` > :p2 AND `t0`.`id` IN (SELECT `t0`.`id` FROM `bans` AS `t0` WHERE `t0`.`abc` = :p3) AND `t0`.`regdt` = :p4)) AND `t0`.`funny` = :p5)', $q->sql());
 }

 /**
  * Loads the tag mapping data from the database, if it hasn't done so already.
  * @return bool
  * @access public
  */
 function load()
 {
     if ($this->_loaded) {
         return true;
     }
     // load the data
     $query = new SelectQuery();
     $query->addTable("dm_tag_map");
     $query->addTable("dm_record_field", INNER_JOIN, "dm_tag_map.fk_record_field=dm_record_field.id");
     $query->addTable("dm_schema_field", INNER_JOIN, "dm_record_field.fk_schema_field=dm_schema_field.id");
     $query->addColumn("index", "record_field_index", "dm_record_field");
     $query->addColumn("id", "record_field_id", "dm_record_field");
     $query->addColumn("id", "schema_field_id", "dm_schema_field");
     $query->setWhere("fk_tag='" . addslashes($this->_myID) . "'");
     $dbHandler = Services::getService("DatabaseManager");
     $result = $dbHandler->query($query, DATAMANAGER_DBID);
     if (!$result) {
         throwError(new UnknownDBError("RecordTag"));
     }
     $tagRows = array();
     while ($result->hasMoreRows()) {
         $a = $result->getCurrentRow();
         $result->advanceRow();
         $tagRows[] = $a;
     }
     $result->free();
     $this->populate($tagRows);
     return true;
 }

 public function testSqlFunctions()
 {
     $f = new SqlFunction('substring', array(new Field('name'), 5, 2));
     $params = array();
     $this->assertEquals('SUBSTRING(`t0`.`name`, 5, 2)', $f->getSql($params));
     $this->assertEquals('substring', $f->getName());
     try {
         $f = new SqlFunction('there_is_no_such_function', new Field('name'));
         $this->fail();
     } catch (InvalidArgumentException $e) {
     }
     try {
         $f = new SqlFunction('substring', new AndOp(array()));
         $this->fail('wrong value accepted');
     } catch (InvalidArgumentException $e) {
     }
     $q = new SelectQuery('test');
     $q->setWhere(new Condition('>', new SqlFunction('length', new Field('foo')), 5));
     $params = array();
     $this->assertEquals('SELECT `t0`.* FROM `test` AS `t0` WHERE LENGTH(`t0`.`foo`) > :p1', $q->sql());
     $parameters = $q->parameters();
     $this->assertEquals('5', $parameters[':p1']);
 }

 /**
  * Deletes all tags stored in the database for {@link DMRecord} id $id.
  * @access public
  * @param int $id
  * @return void
  */
 function deleteRecordTags($id)
 {
     if (!$id) {
         return;
     }
     // first get a list of RecordTag IDs for this dataset.
     $query = new SelectQuery();
     $query->addTable("dm_tag");
     $query->addColumn("id");
     $query->setWhere("fk_record='" . addslashes($id) . "'");
     $dbHandler = Services::getService("DatabaseManager");
     $res = $dbHandler->query($query, DATAMANAGER_DBID);
     $ids = array();
     while ($res->hasMoreRows()) {
         $ids[] = $res->field(0);
         $res->advanceRow();
     }
     $res->free();
     // now delete the datasets
     $query = new DeleteQuery();
     $query->setTable("dm_tag");
     $query->setWhere("fk_record='" . addslashes($id) . "'");
     $dbHandler->query($query, DATAMANAGER_DBID);
     // and delete the mappings
     $wheres = array();
     foreach ($ids as $tagID) {
         $wheres[] = "fk_tag='" . addslashes($tagID) . "'";
     }
     $query->reset();
     $query->setTable("dm_tag_map");
     $query->setWhere(implode(" OR ", $wheres));
     $dbHandler->query($query, DATAMANAGER_DBID);
 }

 /**
  * Will load the data structures for multiple {@link Schema}s.
  * @param ref object An array containing the list of types IDs to be loaded.
  * @return void
  * @access public
  */
 function loadMultiple($preloadTypes)
 {
     $ids = $preloadTypes;
     if (count($ids)) {
         // let's do it
         $query = new SelectQuery();
         $query->addTable("dm_schema_field");
         $query->addColumn("id", "id", "dm_schema_field");
         $query->addColumn("name", "label", "dm_schema_field");
         $query->addColumn("mult", "mult", "dm_schema_field");
         $query->addColumn("required", "required", "dm_schema_field");
         $query->addColumn("active", "active", "dm_schema_field");
         $query->addColumn("fieldtype", "fieldtype", "dm_schema_field");
         $query->addColumn("fk_schema", "fk_schema", "dm_schema_field");
         $wheres = array();
         foreach ($ids as $id) {
             $wheres[] = "fk_schema='" . addslashes($id) . "'";
         }
         $query->setWhere("(" . implode(" OR ", $wheres) . ")");
         //			print "<PRE>".MySQL_SQLGenerator::generateSQLQuery($query)."</PRE>";
         $dbHandler = Services::getService("DatabaseManager");
         $res = $dbHandler->query($query, DATAMANAGER_DBID);
         $rows = array();
         while ($res->hasMoreRows()) {
             $row = $res->getCurrentRow();
             $res->advanceRow();
             $theID = $row["fk_schema"];
             if (!isset($rows[$theID])) {
                 $rows[$theID] = array();
             }
             $rows[$theID][] = $row;
         }
         $res->free();
         //			printpre($rows);
         // now distribute the rows among their respective objects
         foreach (array_keys($rows) as $id) {
             $obj = $this->getSchema($id);
             if (!$obj->loaded()) {
                 $obj->populate($rows[$id]);
             }
         }
     }
 }