PHP SelectQuery::addWhereIn方法代码示例

 /**
  * Load the next bunch of items
  * 
  * @return void
  * @access public
  * @since 3/1/06
  */
 function loadNext()
 {
     $dbc = Services::getService("DatabaseManager");
     // get the list of the next set of Ids
     $query = new SelectQuery();
     $query->addTable("log_entry");
     $query->addColumn("id", "entry_id", "log_entry");
     $query->setDistinct(true);
     $query->addOrderBy("timestamp", DESCENDING);
     $this->addWhereClauses($query);
     $query->limitNumberOfRows($this->_numPerLoad);
     if ($this->_currentRow) {
         $query->startFromRow($this->_currentRow + 1);
     }
     // 		printpre('CurrentRow at load: '.$this->_currentRow);
     // 		printpre($query->asString());
     $results = $dbc->query($query, $this->_dbIndex);
     $nextIds = array();
     while ($results->hasNext()) {
         $row = $results->next();
         $nextIds[] = $row['entry_id'];
     }
     // 		printpre($nextIds);
     /*********************************************************
      * Load the rows for the next set of Ids
      *********************************************************/
     $query = new SelectQuery();
     $query->addTable("log_entry");
     $query->addColumn("id", "id", "log_entry");
     $query->addColumn("timestamp", "timestamp", "log_entry");
     $query->addColumn("category", "category", "log_entry");
     $query->addColumn("description", "description", "log_entry");
     $query->addColumn("backtrace", "backtrace", "log_entry");
     $subQuery = new SelectQuery();
     $subQuery->addColumn("*");
     $subQuery->addTable("log_agent");
     $subQuery->addWhereIn("fk_entry", $nextIds);
     $query->addDerivedTable($subQuery, LEFT_JOIN, "log_entry.id = tmp_agent.fk_entry", "tmp_agent");
     $query->addColumn("fk_agent", "agent_id", "tmp_agent");
     $subQuery = new SelectQuery();
     $subQuery->addColumn("*");
     $subQuery->addTable("log_node");
     $subQuery->addWhereIn("fk_entry", $nextIds);
     $query->addDerivedTable($subQuery, LEFT_JOIN, "log_entry.id = tmp_node.fk_entry", "tmp_node");
     $query->addColumn("fk_node", "node_id", "tmp_node");
     $query->addWhereIn("id", $nextIds);
     $query->addOrderBy("timestamp", DESCENDING);
     $query->addOrderBy("id", ASCENDING);
     //  		printpre($query->asString());
     $results = $dbc->query($query, $this->_dbIndex);
     $i = $this->_current;
     $currentEntryId = null;
     $timestamp = null;
     $category = null;
     $description = null;
     $backtrace = '';
     $agents = array();
     $nodes = array();
     while ($results->hasNext()) {
         $row = $results->next();
         // Create the entry if we have all of the data for it.
         if ($currentEntryId && $currentEntryId != $row["id"]) {
             // 				printpre("Creating Entry: ".$currentEntryId." ".$timestamp." -- ".($i+1)." of ".$this->_count);
             $this->_entries[$i] = new HarmoniEntry($dbc->fromDBDate($timestamp, $this->_dbIndex), $category, $description, $backtrace, array_unique($agents), array_unique($nodes), $this->_formatType, $this->_priorityType);
             $i++;
             $this->_currentRow++;
             $currentEntryId = null;
             $timestamp = null;
             $category = null;
             $description = null;
             $backtrace = '';
             $agents = array();
             $nodes = array();
         }
         $currentEntryId = $row["id"];
         $timestamp = $row["timestamp"];
         $category = $row["category"];
         $description = $row["description"];
         $backtrace = $row["backtrace"];
         $agents[] = $row["agent_id"];
         $nodes[] = $row["node_id"];
         // 			printpre($currentEntryId." ".$timestamp." ".$this->_currentRow);
     }
     $results->free();
     // get the last entry if we are at the end of the iterator
     if ($currentEntryId && $i == $this->_count - 1) {
         // 			printpre("Creating Entry: ".$currentEntryId." ".$timestamp." -- ".($i+1)." of ".$this->_count);
         $this->_entries[$i] = new HarmoniEntry($dbc->fromDBDate($timestamp, $this->_dbIndex), $category, $description, $backtrace, array_unique($agents), array_unique($nodes), $this->_formatType, $this->_priorityType);
     }
 }

 /**
  * Load authorizations for multiple qualifiers
  * 
  * @param string $agentIdString
  * @param array $qualifierIdStrings
  * @return void
  * @access public
  * @since 4/29/08
  */
 public function _loadMultiple($agentIdString, array $qualifierIdStrings)
 {
     $dbHandler = Services::getService("DatabaseManager");
     $dbIndex = $this->_configuration->getProperty('database_index');
     $agentIdStrings = $this->getAgentIdStringArray($agentIdString);
     /*********************************************************
      * Explicit AZs
      *********************************************************/
     // Select and create all of the explicit AZs
     $query = new SelectQuery();
     $query->addColumn("*");
     $query->addTable("az2_explicit_az");
     $query->addWhereIn("fk_agent", $agentIdStrings);
     $query->addWhere("(effective_date IS NULL OR effective_date < NOW())");
     $query->addWhere("(expiration_date IS NULL OR expiration_date > NOW())");
     $query->addWhereIn("fk_qualifier", $qualifierIdStrings);
     // 		printpre(MySQL_SQLGenerator::generateSQLQuery($query));
     $result = $dbHandler->query($query, $dbIndex);
     // Create the explicit AZs
     while ($result->hasMoreRows()) {
         // Set a boolean for the AZ.
         if (!isset($_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_qualifier")])) {
             $_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_qualifier")] = array();
         }
         $_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_qualifier")][$result->field("fk_function")] = true;
         $result->advanceRow();
     }
     $result->free();
     /*********************************************************
      * Implicit AZs	
      *********************************************************/
     // Select and create all of the explicit AZs
     $query = new SelectQuery();
     $query->addColumn("*");
     $query->addTable("az2_implicit_az");
     $query->addWhereIn("fk_agent", $agentIdStrings);
     $query->addWhere("(effective_date IS NULL OR effective_date < NOW())");
     $query->addWhere("(expiration_date IS NULL OR expiration_date > NOW())");
     $query->addWhereIn("fk_qualifier", $qualifierIdStrings);
     // 		printpre(MySQL_SQLGenerator::generateSQLQuery($query));
     $result = $dbHandler->query($query, $dbIndex);
     // Create the explicit AZs
     while ($result->hasMoreRows()) {
         // Set a boolean for the AZ.
         if (!isset($_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_qualifier")])) {
             $_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_qualifier")] = array();
         }
         $_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_qualifier")][$result->field("fk_function")] = true;
         $result->advanceRow();
     }
     $result->free();
 }

 /**
  * Answer the Hierarchy-based parent-group for an external group Id.
  * 
  * @param array $groupIds An array of Ids
  * @return array an array of parent group Ids
  * @access public
  * @since 11/6/07
  */
 public function getHierarchyParentIdsForExternalGroups(array $groupIds)
 {
     if (!count($groupIds)) {
         return array();
     }
     $query = new SelectQuery();
     $query->addTable('agent_external_children');
     $query->addColumn('DISTINCT fk_parent', 'parent');
     // Before PHP 5.2.0, __toString() was only called automatically
     // in print() statements, not in concatinations.
     if (version_compare(phpversion(), '5.2.0', '<')) {
         foreach ($groupIds as $key => $val) {
             if (is_object($val)) {
                 $groupIds[$key] = $val->__toString();
             }
         }
     }
     $query->addWhereIn('fk_child', $groupIds);
     $dbc = Services::getService("DBHandler");
     $result = $dbc->query($query, $this->_configuration->getProperty('database_index'));
     $idMgr = Services::getService("Id");
     $parents = array();
     while ($result->hasMoreRows()) {
         $parents[] = $idMgr->getId($result->field('parent'));
         $result->advanceRow();
     }
     return $parents;
 }

 /**
  * Answer the internal slot definitions for the shortnames passed
  * 
  * @param array $shortnames An array of strings
  * @return array
  * @access private
  * @since 1/4/08
  */
 private function getInternalSlotDefinitionsForShortnames(array $shortnames)
 {
     if (!count($shortnames)) {
         return array();
     }
     $query = new SelectQuery();
     $query->addTable('segue_slot');
     $query->addTable('segue_slot_owner AS all_owners', LEFT_JOIN, 'segue_slot.shortname = all_owners.shortname');
     $query->addColumn('segue_slot.shortname', 'shortname');
     $query->addColumn('segue_slot.site_id', 'site_id');
     $query->addColumn('segue_slot.alias_target', 'alias_target');
     $query->addColumn('segue_slot.type', 'type');
     $query->addColumn('segue_slot.location_category', 'location_category');
     $query->addColumn('segue_slot.media_quota', 'media_quota');
     $query->addColumn('all_owners.owner_id', 'owner_id');
     $query->addColumn('all_owners.removed', 'removed');
     $query->addWhereIn('segue_slot.shortname', $shortnames);
     // 		printpre($query->asString());
     $dbc = Services::getService('DBHandler');
     $result = $dbc->query($query, IMPORTER_CONNECTION);
     return $this->getSlotsFromQueryResult($result);
 }

//.........这里部分代码省略.........
     // 					$rootNode->getId(),
     // 					Hierarchy::TRAVERSE_MODE_DEPTH_FIRST,
     // 					Hierarchy::TRAVERSE_DIRECTION_DOWN,
     // 					Hierarchy::TRAVERSE_LEVELS_ALL);
     // 				$timer2->end();
     // 				printf("<br/>CacheAZ Traversal Time: %1.6f", $timer2->printTime());
     //
     // 				$explicitAZLevels = array();
     //
     // 				while ($traversal->hasNext()) {
     // 					$info =$traversal->next();
     // 					$id =$info->getNodeId();
     // 					$idString = $id->getIdString();
     // 					$level = $info->getLevel();
     // // 					printpre("<strong>$level\t$idString</strong>");
     //
     // 					foreach($functions as $functionId) {
     // 						if (!isset($explicitAZLevels[$functionId])) {
     // 							if (isset($_SESSION['__isAuthorizedCache'][$agentIdString][$idString][$functionId])) {
     // 								$explicitAZLevels[$functionId] = $level;
     // // 								printpre("\tFound Explicit $functionId at level $level");
     // 							}
     // 						} else {
     // 							if ($level <= $explicitAZLevels[$functionId]) {
     // 								unset($explicitAZLevels[$functionId]);
     // // 								printpre("\tUnsetting ExplicitAZ $functionId at $level");
     // 							} else {
     // 								$_SESSION['__isAuthorizedCache'][$agentIdString][$idString][$functionId] = true;
     // // 								printpre("\tSetting Implicit $functionId at level $level");
     // 							}
     // 						}
     // 					}
     // 				}
     // 			}
     // 		}
     // Before we do the big work to find the implicit AZs, first check that the
     // node Ids we are looking for exist. If not, make note of this and do not search
     // for them.
     $query = new SelectQuery();
     $query->addColumn("DISTINCT node_id");
     $query->addTable("node");
     $query->addWhereIn("node_id", $this->_queue[$agentIdString]);
     $result = $dbHandler->query($query, $this->_configuration->getProperty('database_index'));
     $foundNodes = array();
     while ($result->hasMoreRows()) {
         $foundNodes[] = $result->field('node_id');
         $result->advanceRow();
     }
     $result->free();
     // Get a list of missing nodes
     $missing = array_diff($this->_queue[$agentIdString], $foundNodes);
     foreach ($missing as $nodeId) {
         $_SESSION['__isAuthorizedCacheUnknownIds'][] = $nodeId;
     }
     // Algorithm B:
     // For this algorithm we want to join all of the explicit AZs to all
     // nodes who have the qulifier as an ancestor. These will be the implicit AZs
     if (count($foundNodes)) {
         $query = new SelectQuery();
         $query->addColumn("authorization_id");
         $query->addColumn("fk_node");
         $query->addTable("az_authorization");
         $query->addTable("node_ancestry", LEFT_JOIN, "fk_qualifier = fk_ancestor");
         $query->addWhereIn("fk_node", $foundNodes);
         $agentIdStrings = $this->getAgentIdStringArray($agentIdString);
         foreach ($agentIdStrings as $key => $val) {
             $agentIdStrings[$key] = "'" . addslashes($val) . "'";
         }
         $query->addWhere("fk_agent IN(" . implode(", ", $agentIdStrings) . ")");
         $query->addWhere("(authorization_effective_date IS NULL OR authorization_effective_date < NOW())");
         $query->addWhere("(authorization_expiration_date IS NULL OR authorization_expiration_date > NOW())");
         // 		printpre(MySQL_SQLGenerator::generateSQLQuery($query));
         $result = $dbHandler->query($query, $this->_configuration->getProperty('database_index'));
         while ($result->hasMoreRows()) {
             $explicitAZ = $explicitAZs[$result->field("authorization_id")];
             $explicitFunction = $explicitAZ->getFunction();
             $explicitFunctionId = $explicitFunction->getId();
             // cache in our user AZ cache
             if (!isset($_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_node")])) {
                 $_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_node")] = array();
             }
             $_SESSION['__isAuthorizedCache'][$agentIdString][$result->field("fk_node")][$explicitFunctionId->getIdString()] = true;
             $result->advanceRow();
         }
         $result->free();
         // Set flags that each Qualifier in the queue has had its implicit AZs cached.
         foreach ($this->_queue[$agentIdString] as $qualifierIdString) {
             $_SESSION['__isAuthorizedCache'][$agentIdString][$qualifierIdString]['__IMPLICIT_CACHED'] = true;
         }
     }
     // 		$timer->end();
     // 		printf("<br/>CacheAZTime: %1.6f", $timer->printTime());
     // 		print "<br/>Num Queries: ".($dbHandler->getTotalNumberOfQueries() - $startingQueries);
     $this->_queue[$agentIdString] = array();
     // 		@$this->ticker++;
     // 		if ($this->ticker > 100) {
     // 			printpre($_SESSION['__isAuthorizedCache'][$agentIdString]);
     // 			exit;
     // 		}
 }

 /**
  * Auxilliary private function that returns Authorizations according to a
  * criteria. Null values are interpreted as wildmarks. Warning: $returnExplicitOnly = false
  * will increase the running time significantly - USE SPARINGLY!
  * @access public
  * @param string aId The string id of an agent.
  * @param string fId The string id of a function.
  * @param string qId The string id of a qualifier. This parameter can not be null
  * and used as a wildmark.
  * @param object fType The type of a function.
  * @param boolean returnExplicitOnly If True, only explicit Authorizations
  *		will be returned.
  * @param boolean searchUp If true, the ancester nodes of the qualifier will
  *		be checked as well
  * @param boolean isActiveNow If True, only active Authorizations will be returned.
  * @return ref object An AuthorizationIterator.
  **/
 function getAZs($aId, $fId, $qId, $fType, $returnExplicitOnly, $searchUp, $isActiveNow, $groupIds = array())
 {
     // 		printpre (func_get_args());
     // ** parameter validation
     $rule = StringValidatorRule::getRule();
     ArgumentValidator::validate($groupIds, ArrayValidatorRuleWithRule::getRule(OptionalRule::getRule($rule)), true);
     ArgumentValidator::validate($aId, OptionalRule::getRule($rule), true);
     ArgumentValidator::validate($fId, OptionalRule::getRule($rule), true);
     ArgumentValidator::validate($qId, OptionalRule::getRule($rule), true);
     ArgumentValidator::validate($fType, OptionalRule::getRule(ExtendsValidatorRule::getRule("Type")), true);
     ArgumentValidator::validate($returnExplicitOnly, BooleanValidatorRule::getRule(), true);
     ArgumentValidator::validate($isActiveNow, BooleanValidatorRule::getRule(), true);
     // ** end of parameter validation
     $idManager = Services::getService("Id");
     // the parameter that influences the result most is $returnExplicitOnly
     // 1) If $returnExplicitOnly is TRUE, then we only need to check for Authorizations
     // that have been explicitly created, i.e. no need to look for inherited
     // authorizations
     // 2) If $returnExplicitOnly is FALSE, then we need to include inherited Authorizations
     // as well.
     // this array will store the ids of all qualifiers to be checked for authorizations
     $qualifiers = array();
     // check all ancestors of given qualifier
     $hierarchyManager = Services::getService("Hierarchy");
     if (isset($qId)) {
         $qualifierId = $idManager->getId($qId);
         $node = $hierarchyManager->getNode($qualifierId);
         $hierarchy = $hierarchyManager->getHierarchyForNode($node);
         if ($searchUp) {
             // these are the ancestor nodes
             $nodes = $hierarchy->traverse($qualifierId, Hierarchy::TRAVERSE_MODE_DEPTH_FIRST, Hierarchy::TRAVERSE_DIRECTION_UP, Hierarchy::TRAVERSE_LEVELS_ALL);
             // now get the id of each node and store in array
             while ($nodes->hasNext()) {
                 $info = $nodes->next();
                 $id = $info->getNodeId();
                 $qualifiers[] = $id->getIdString();
             }
         } else {
             $qualifiers = array($qId);
         }
     }
     //		print_r($qualifiers);
     // setup the query
     $dbHandler = Services::getService("DatabaseManager");
     $query = new SelectQuery();
     $query->addColumn("authorization_id", "id");
     $query->addColumn("fk_agent", "aid");
     $query->addColumn("fk_function", "fid");
     $query->addColumn("fk_qualifier", "qid");
     $query->addColumn("authorization_effective_date", "eff_date");
     $query->addColumn("authorization_expiration_date", "exp_date");
     $query->addTable("az_authorization");
     // now include criteria
     // the qualifiers criteria
     if (isset($qualifiers) && count($qualifiers)) {
         $query->addWhereIn('az_authorization.fk_qualifier', $qualifiers);
     }
     // Agents/Groups
     if (isset($aId)) {
         $agentIds = array($aId);
     } else {
         $agentIds = array();
     }
     $allAgentIds = array_merge($agentIds, $groupIds);
     // the agent criteria
     if (count($allAgentIds)) {
         $query->addWhereIn('az_authorization.fk_agent', $allAgentIds);
     }
     // the function criteria
     if (isset($fId)) {
         $joinc = "az_authorization.fk_function = az_function.function_id";
         $query->addTable("az_function", INNER_JOIN, $joinc);
         $query->addWhereEqual("az_authorization.fk_function", $fId);
     }
     // the function type criteria
     if (isset($fType)) {
         // do not join with az_function if we did already
         if (!isset($fId)) {
             $joinc = "az_authorization.fk_function = az_function.function_id";
             $query->addTable("az_function", INNER_JOIN, $joinc);
         }
         // now join with type
         $joinc = "az_function.fk_type = type.type_id";
//.........这里部分代码省略.........

 /**
  * Answer An AZ query for implicit AZs
  * 
  * @param array $agentIds The agent Ids to match
  * @param string fId The string id of a function.
  * @param string qId The string id of a qualifier. This parameter can not be null
  * and used as a wildmark.
  * @param object fType The type of a function.
  * @param boolean isActiveNow If True, only active Authorizations will be returned.
  * @return SelectQueryInterface 
  * @access protected
  * @since 4/23/08
  */
 protected function getImplicitAZQuery(array $agentIds, $fId, $qId, $fType, $isActiveNow)
 {
     $query = new SelectQuery();
     $query->addColumn("fk_explicit_az");
     $query->addColumn("id", "id", "az2_implicit_az");
     $query->addColumn("fk_agent", "aid");
     $query->addColumn("fk_function", "fid");
     $query->addColumn("fk_qualifier", "qid");
     $query->addColumn("effective_date", "eff_date");
     $query->addColumn("expiration_date", "exp_date");
     $query->addTable("az2_implicit_az");
     // now include criteria
     // the qualifiers criteria
     if (isset($qId)) {
         $query->addWhereEqual('fk_qualifier', $qId);
     }
     // the agent criteria
     if (count($agentIds)) {
         $query->addWhereIn('fk_agent', $agentIds);
     }
     // the function criteria
     if (isset($fId)) {
         $joinc = "az2_implicit_az.fk_function = az2_function.id";
         $query->addTable("az2_function", INNER_JOIN, $joinc);
         $query->addWhereEqual("fk_function", $fId);
     }
     // the function type criteria
     if (isset($fType)) {
         // do not join with az_function if we did already
         if (!isset($fId)) {
             $joinc = "az2_implicit_az.fk_function = az2_function.id";
             $query->addTable("az2_function", INNER_JOIN, $joinc);
         }
         // now join with type
         $joinc = "az2_function.fk_type = az2_function_type.id";
         $query->addTable("az2_function_type", INNER_JOIN, $joinc);
         $query->addWhereEqual("domain", $fType->getDomain());
         $query->addWhereEqual("authority", $fType->getAuthority());
         $query->addWhereEqual("keyword", $fType->getKeyword());
     }
     // the isActiveNow criteria
     if ($isActiveNow) {
         $where = "(effective_date IS NULL OR (NOW() >= effective_date))";
         $query->addWhere($where);
         $where = "(expiration_date IS NULL OR (NOW() < expiration_date))";
         $query->addWhere($where);
     }
     $query->addOrderBy("az2_implicit_az.id");
     return $query;
 }