protected function DoExecute() { $oQuery = new SQLObjectQuery($sTable = 'myTable', $sTableAlias = 'myTableAlias', $aFields = array('column1' => new FieldExpression('column1', 'myTableAlias'), 'column2' => new FieldExpression('column2', 'myTableAlias')), $bToDelete = false, $aValues = array()); $oQuery->AddCondition(Expression::FromOQL('DATE(NOW() - 1200 * 2) > \'2008-07-31\'')); $oSubQuery1 = new SQLObjectQuery($sTable = 'myTable1', $sTableAlias = 'myTable1Alias', $aFields = array('column1_1' => new FieldExpression('column1', 'myTableAlias'), 'column1_2' => new FieldExpression('column1', 'myTableAlias')), $bToDelete = false, $aValues = array()); $oSubQuery2 = new SQLObjectQuery($sTable = 'myTable2', $sTableAlias = 'myTable2Alias', $aFields = array('column2_1' => new FieldExpression('column2', 'myTableAlias'), 'column2_2' => new FieldExpression('column2', 'myTableAlias')), $bToDelete = false, $aValues = array()); $oQuery->AddInnerJoin($oSubQuery1, 'column1', 'column1_1'); $oQuery->AddLeftJoin($oSubQuery2, 'column2', 'column2_2'); $oQuery->DisplayHtml(); $oQuery->RenderDelete(); $oQuery->RenderUpdate(); echo '<p>' . $oQuery->RenderSelect() . '</p>'; $oQuery->RenderSelect(array('column1')); $oQuery->RenderSelect(array('column1', 'column2')); }
/** * Describe (as a text string) the modifications corresponding to this change */ public function GetDescription() { $sResult = ''; $oTargetObjectClass = $this->Get('objclass'); $oTargetObjectKey = $this->Get('objkey'); $oTargetSearch = new DBObjectSearch($oTargetObjectClass); $oTargetSearch->AddCondition('id', $oTargetObjectKey, '='); $oMonoObjectSet = new DBObjectSet($oTargetSearch); if (UserRights::IsActionAllowedOnAttribute($this->Get('objclass'), $this->Get('attcode'), UR_ACTION_READ, $oMonoObjectSet) == UR_ALLOWED_YES) { if (!MetaModel::IsValidAttCode($this->Get('objclass'), $this->Get('attcode'))) { return ''; } // Protects against renamed attributes... $oAttDef = MetaModel::GetAttributeDef($this->Get('objclass'), $this->Get('attcode')); $sAttName = $oAttDef->GetLabel(); $sLinkClass = $oAttDef->GetLinkedClass(); $aLinkClasses = MetaModel::EnumChildClasses($sLinkClass, ENUM_CHILD_CLASSES_ALL); // Search for changes on the corresponding link // $oSearch = new DBObjectSearch('CMDBChangeOpSetAttribute'); $oSearch->AddCondition('change', $this->Get('change'), '='); $oSearch->AddCondition('objkey', $this->Get('link_id'), '='); if (count($aLinkClasses) == 1) { // Faster than the whole building of the expression below for just one value ?? $oSearch->AddCondition('objclass', $sLinkClass, '='); } else { $oField = new FieldExpression('objclass', $oSearch->GetClassAlias()); $sListExpr = '(' . implode(', ', CMDBSource::Quote($aLinkClasses)) . ')'; $sOQLCondition = $oField->Render() . " IN {$sListExpr}"; $oNewCondition = Expression::FromOQL($sOQLCondition); $oSearch->AddConditionExpression($oNewCondition); } $oSet = new DBObjectSet($oSearch); $aChanges = array(); while ($oChangeOp = $oSet->Fetch()) { $aChanges[] = $oChangeOp->GetDescription(); } if (count($aChanges) == 0) { return ''; } $sItemDesc = MetaModel::GetHyperLink($this->Get('item_class'), $this->Get('item_id')); $sResult = $sAttName . ' - '; $sResult .= Dict::Format('Change:LinkSet:Modified', $sItemDesc); $sResult .= ' : ' . implode(', ', $aChanges); } return $sResult; }
protected static function GetConditionIN($oFilter, $sFilterCode, $condition) { $oField = new FieldExpression($sFilterCode, $oFilter->GetClassAlias()); $sListExpr = '(' . implode(', ', CMDBSource::Quote($condition)) . ')'; $sOQLCondition = $oField->Render() . " IN {$sListExpr}"; $oNewCondition = Expression::FromOQL($sOQLCondition); return $oNewCondition; }
protected function MakeSQLObjectQuery(&$oBuild, $aAttToLoad = null, $aValues = array()) { // Note: query class might be different than the class of the filter // -> this occurs when we are linking our class to an external class (referenced by, or pointing to) $sClass = $this->GetFirstJoinedClass(); $sClassAlias = $this->GetFirstJoinedClassAlias(); $bIsOnQueriedClass = array_key_exists($sClassAlias, $oBuild->GetRootFilter()->GetSelectedClasses()); self::DbgTrace("Entering: " . $this->ToOQL() . ", " . ($bIsOnQueriedClass ? "MAIN" : "SECONDARY")); $sRootClass = MetaModel::GetRootClass($sClass); $sKeyField = MetaModel::DBGetKey($sClass); if ($bIsOnQueriedClass) { // default to the whole list of attributes + the very std id/finalclass $oBuild->m_oQBExpressions->AddSelect($sClassAlias . 'id', new FieldExpression('id', $sClassAlias)); if (is_null($aAttToLoad) || !array_key_exists($sClassAlias, $aAttToLoad)) { $sSelectedClass = $oBuild->GetSelectedClass($sClassAlias); $aAttList = MetaModel::ListAttributeDefs($sSelectedClass); } else { $aAttList = $aAttToLoad[$sClassAlias]; } foreach ($aAttList as $sAttCode => $oAttDef) { if (!$oAttDef->IsScalar()) { continue; } // keep because it can be used for sorting - if (!$oAttDef->LoadInObject()) continue; foreach ($oAttDef->GetSQLExpressions() as $sColId => $sSQLExpr) { $oBuild->m_oQBExpressions->AddSelect($sClassAlias . $sAttCode . $sColId, new FieldExpression($sAttCode . $sColId, $sClassAlias)); } } // Transform the full text condition into additional condition expression $aFullText = $this->GetCriteria_FullText(); if (count($aFullText) > 0) { $aFullTextFields = array(); foreach (MetaModel::ListAttributeDefs($sClass) as $sAttCode => $oAttDef) { if (!$oAttDef->IsScalar()) { continue; } if ($oAttDef->IsExternalKey()) { continue; } $aFullTextFields[] = new FieldExpression($sAttCode, $sClassAlias); } $oTextFields = new CharConcatWSExpression(' ', $aFullTextFields); foreach ($aFullText as $sFTNeedle) { $oNewCond = new BinaryExpression($oTextFields, 'LIKE', new ScalarExpression("%{$sFTNeedle}%")); $oBuild->m_oQBExpressions->AddCondition($oNewCond); } } } //echo "<p>oQBExpr ".__LINE__.": <pre>\n".print_r($oBuild->m_oQBExpressions, true)."</pre></p>\n"; $aExpectedAtts = array(); // array of (attcode => fieldexpression) //echo "<p>".__LINE__.": GetUnresolvedFields($sClassAlias, ...)</p>\n"; $oBuild->m_oQBExpressions->GetUnresolvedFields($sClassAlias, $aExpectedAtts); // Compute a clear view of required joins (from the current class) // Build the list of external keys: // -> ext keys required by an explicit join // -> ext keys mentionned in a 'pointing to' condition // -> ext keys required for an external field // -> ext keys required for a friendly name // $aExtKeys = array(); // array of sTableClass => array of (sAttCode (keys) => array of (sAttCode (fields)=> oAttDef)) // // Optimization: could be partially computed once for all (cached) ? // if ($bIsOnQueriedClass) { // Get all Ext keys for the queried class (??) foreach (MetaModel::GetKeysList($sClass) as $sKeyAttCode) { $sKeyTableClass = MetaModel::GetAttributeOrigin($sClass, $sKeyAttCode); $aExtKeys[$sKeyTableClass][$sKeyAttCode] = array(); } } // Get all Ext keys used by the filter foreach ($this->GetCriteria_PointingTo() as $sKeyAttCode => $aPointingTo) { if (array_key_exists(TREE_OPERATOR_EQUALS, $aPointingTo)) { $sKeyTableClass = MetaModel::GetAttributeOrigin($sClass, $sKeyAttCode); $aExtKeys[$sKeyTableClass][$sKeyAttCode] = array(); } } $aFNJoinAlias = array(); // array of (subclass => alias) if (array_key_exists('friendlyname', $aExpectedAtts)) { // To optimize: detect a restriction on child classes in the condition expression // e.g. SELECT FunctionalCI WHERE finalclass IN ('Server', 'VirtualMachine') $oNameExpression = self::GetExtendedNameExpression($sClass); $aNameFields = array(); $oNameExpression->GetUnresolvedFields('', $aNameFields); $aTranslateNameFields = array(); foreach ($aNameFields as $sSubClass => $aFields) { foreach ($aFields as $sAttCode => $oField) { $oAttDef = MetaModel::GetAttributeDef($sSubClass, $sAttCode); if ($oAttDef->IsExternalKey()) { $sClassOfAttribute = MetaModel::GetAttributeOrigin($sSubClass, $sAttCode); $aExtKeys[$sClassOfAttribute][$sAttCode] = array(); } elseif ($oAttDef->IsExternalField() || $oAttDef instanceof AttributeFriendlyName) { $sKeyAttCode = $oAttDef->GetKeyAttCode(); $sClassOfAttribute = MetaModel::GetAttributeOrigin($sSubClass, $sKeyAttCode); $aExtKeys[$sClassOfAttribute][$sKeyAttCode][$sAttCode] = $oAttDef; } else { $sClassOfAttribute = MetaModel::GetAttributeOrigin($sSubClass, $sAttCode); } if (MetaModel::IsParentClass($sClassOfAttribute, $sClass)) { // The attribute is part of the standard query // $sAliasForAttribute = $sClassAlias; } else { // The attribute will be available from an additional outer join // For each subclass (table) one single join is enough // if (!array_key_exists($sClassOfAttribute, $aFNJoinAlias)) { $sAliasForAttribute = $oBuild->GenerateClassAlias($sClassAlias . '_fn_' . $sClassOfAttribute, $sClassOfAttribute); $aFNJoinAlias[$sClassOfAttribute] = $sAliasForAttribute; } else { $sAliasForAttribute = $aFNJoinAlias[$sClassOfAttribute]; } } $aTranslateNameFields[$sSubClass][$sAttCode] = new FieldExpression($sAttCode, $sAliasForAttribute); } } $oNameExpression = $oNameExpression->Translate($aTranslateNameFields, false); $aTranslateNow = array(); $aTranslateNow[$sClassAlias]['friendlyname'] = $oNameExpression; $oBuild->m_oQBExpressions->Translate($aTranslateNow, false); } // Add the ext fields used in the select (eventually adds an external key) foreach (MetaModel::ListAttributeDefs($sClass) as $sAttCode => $oAttDef) { if ($oAttDef->IsExternalField() || $oAttDef instanceof AttributeFriendlyName) { if (array_key_exists($sAttCode, $aExpectedAtts)) { $sKeyAttCode = $oAttDef->GetKeyAttCode(); if ($sKeyAttCode != 'id') { // Add the external attribute $sKeyTableClass = MetaModel::GetAttributeOrigin($sClass, $sKeyAttCode); $aExtKeys[$sKeyTableClass][$sKeyAttCode][$sAttCode] = $oAttDef; } } } } // First query built upon on the leaf (ie current) class // self::DbgTrace("Main (=leaf) class, call MakeSQLObjectQuerySingleTable()"); if (MetaModel::HasTable($sClass)) { $oSelectBase = $this->MakeSQLObjectQuerySingleTable($oBuild, $aAttToLoad, $sClass, $aExtKeys, $aValues); } else { $oSelectBase = null; // As the join will not filter on the expected classes, we have to specify it explicitely $sExpectedClasses = implode("', '", MetaModel::EnumChildClasses($sClass, ENUM_CHILD_CLASSES_ALL)); $oFinalClassRestriction = Expression::FromOQL("`{$sClassAlias}`.finalclass IN ('{$sExpectedClasses}')"); $oBuild->m_oQBExpressions->AddCondition($oFinalClassRestriction); } // Then we join the queries of the eventual parent classes (compound model) foreach (MetaModel::EnumParentClasses($sClass) as $sParentClass) { if (!MetaModel::HasTable($sParentClass)) { continue; } self::DbgTrace("Parent class: {$sParentClass}... let's call MakeSQLObjectQuerySingleTable()"); $oSelectParentTable = $this->MakeSQLObjectQuerySingleTable($oBuild, $aAttToLoad, $sParentClass, $aExtKeys, $aValues); if (is_null($oSelectBase)) { $oSelectBase = $oSelectParentTable; } else { $oSelectBase->AddInnerJoin($oSelectParentTable, $sKeyField, MetaModel::DBGetKey($sParentClass)); } } // Filter on objects referencing me foreach ($this->GetCriteria_ReferencedBy() as $sForeignClass => $aKeysAndFilters) { foreach ($aKeysAndFilters as $sForeignKeyAttCode => $oForeignFilter) { $oForeignKeyAttDef = MetaModel::GetAttributeDef($sForeignClass, $sForeignKeyAttCode); self::DbgTrace("Referenced by foreign key: {$sForeignKeyAttCode}... let's call MakeSQLObjectQuery()"); //self::DbgTrace($oForeignFilter); //self::DbgTrace($oForeignFilter->ToOQL()); //self::DbgTrace($oSelectForeign); //self::DbgTrace($oSelectForeign->RenderSelect(array())); $sForeignClassAlias = $oForeignFilter->GetFirstJoinedClassAlias(); $oBuild->m_oQBExpressions->PushJoinField(new FieldExpression($sForeignKeyAttCode, $sForeignClassAlias)); if ($oForeignKeyAttDef instanceof AttributeObjectKey) { $sClassAttCode = $oForeignKeyAttDef->Get('class_attcode'); // Add the condition: `$sForeignClassAlias`.$sClassAttCode IN (subclasses of $sClass') $oClassListExpr = ListExpression::FromScalars(MetaModel::EnumChildClasses($sClass, ENUM_CHILD_CLASSES_ALL)); $oClassExpr = new FieldExpression($sClassAttCode, $sForeignClassAlias); $oClassRestriction = new BinaryExpression($oClassExpr, 'IN', $oClassListExpr); $oBuild->m_oQBExpressions->AddCondition($oClassRestriction); } $oSelectForeign = $oForeignFilter->MakeSQLObjectQuery($oBuild, $aAttToLoad); $oJoinExpr = $oBuild->m_oQBExpressions->PopJoinField(); $sForeignKeyTable = $oJoinExpr->GetParent(); $sForeignKeyColumn = $oJoinExpr->GetName(); $oSelectBase->AddInnerJoin($oSelectForeign, $sKeyField, $sForeignKeyColumn, $sForeignKeyTable); } } // Additional JOINS for Friendly names // foreach ($aFNJoinAlias as $sSubClass => $sSubClassAlias) { $oSubClassFilter = new DBObjectSearch($sSubClass, $sSubClassAlias); $oSelectFN = $oSubClassFilter->MakeSQLObjectQuerySingleTable($oBuild, $aAttToLoad, $sSubClass, $aExtKeys, array()); $oSelectBase->AddLeftJoin($oSelectFN, $sKeyField, MetaModel::DBGetKey($sSubClass)); } // That's all... cross fingers and we'll get some working query //MyHelpers::var_dump_html($oSelectBase, true); //MyHelpers::var_dump_html($oSelectBase->RenderSelect(), true); if (self::$m_bDebugQuery) { $oSelectBase->DisplayHtml(); } return $oSelectBase; }
public function AddCondition($sFilterCode, $value, $sOpCode = null) { MyHelpers::CheckKeyInArray('filter code', $sFilterCode, MetaModel::GetClassFilterDefs($this->GetClass())); $oFilterDef = MetaModel::GetClassFilterDef($this->GetClass(), $sFilterCode); $oField = new FieldExpression($sFilterCode, $this->GetClassAlias()); if (empty($sOpCode)) { if ($sFilterCode == 'id') { $sOpCode = '='; } else { $oAttDef = MetaModel::GetAttributeDef($this->GetClass(), $sFilterCode); $oNewCondition = $oAttDef->GetSmartConditionExpression($value, $oField, $this->m_aParams); $this->AddConditionExpression($oNewCondition); return; } } MyHelpers::CheckKeyInArray('operator', $sOpCode, $oFilterDef->GetOperators()); // Preserve backward compatibility - quick n'dirty way to change that API semantic // switch ($sOpCode) { case 'SameDay': case 'SameMonth': case 'SameYear': case 'Today': case '>|': case '<|': case '=|': throw new CoreException('Deprecated operator, please consider using OQL (SQL) expressions like "(TO_DAYS(NOW()) - TO_DAYS(x)) AS AgeDays"', array('operator' => $sOpCode)); break; case "IN": if (!is_array($value)) { $value = array($value); } $sListExpr = '(' . implode(', ', CMDBSource::Quote($value)) . ')'; $sOQLCondition = $oField->Render() . " IN {$sListExpr}"; break; case "NOTIN": if (!is_array($value)) { $value = array($value); } $sListExpr = '(' . implode(', ', CMDBSource::Quote($value)) . ')'; $sOQLCondition = $oField->Render() . " NOT IN {$sListExpr}"; break; case 'Contains': $this->m_aParams[$sFilterCode] = "%{$value}%"; $sOperator = 'LIKE'; break; case 'Begins with': $this->m_aParams[$sFilterCode] = "{$value}%"; $sOperator = 'LIKE'; break; case 'Finishes with': $this->m_aParams[$sFilterCode] = "%{$value}"; $sOperator = 'LIKE'; break; default: $this->m_aParams[$sFilterCode] = $value; $sOperator = $sOpCode; } switch ($sOpCode) { case "IN": case "NOTIN": $oNewCondition = Expression::FromOQL($sOQLCondition); break; case 'Contains': case 'Begins with': case 'Finishes with': default: $oRightExpr = new VariableExpression($sFilterCode); $oNewCondition = new BinaryExpression($oField, $sOperator, $oRightExpr); } $this->AddConditionExpression($oNewCondition); }
/** * Lists all the currently opened User Requests for the current user * @param WebPage $oP The current web page * @return void */ function ListOpenRequests(WebPage $oP) { $oUserOrg = GetUserOrg(); $aClassToSet = array(); $iUser = UserRights::GetContactId(); $oContact = MetaModel::GetObject('Contact', UserRights::GetContactId()); foreach (GetTicketClasses() as $sClass) { if (IsPowerUser()) { $sValidationDefine = 'PORTAL_' . strtoupper($sClass) . '_DISPLAY_POWERUSER_QUERY'; } else { $sValidationDefine = 'PORTAL_' . strtoupper($sClass) . '_DISPLAY_QUERY'; } if (defined($sValidationDefine)) { $sOQL = constant($sValidationDefine); $oSearch = DBObjectSearch::FromOQL($sOQL); $sOQLCondition = $oSearch->GetClassAlias() . ".status NOT IN ('closed', 'resolved')"; $oExpr = Expression::FromOQL($sOQLCondition); $oSearch->AddConditionExpression($oExpr); } else { $sOQL = "SELECT {$sClass} WHERE org_id = :org_id AND status NOT IN ('closed', 'resolved')"; $oSearch = DBObjectSearch::FromOQL($sOQL); if ($iUser > 0 && !IsPowerUser()) { $oSearch->AddCondition('caller_id', $iUser); } } $aClassToSet[$sClass] = new CMDBObjectSet($oSearch, array(), array('org_id' => $oUserOrg->GetKey(), 'caller_id' => $iUser, 'contact' => $oContact)); } DisplayRequestLists($oP, $aClassToSet); }