1<?php 2/* 3 * Copyright Intermesh BV. 4 * 5 * This file is part of Group-Office. You should have received a copy of the 6 * Group-Office license along with Group-Office. See the file /LICENSE.TXT 7 * 8 * If you have questions write an e-mail to info@intermesh.nl 9 */ 10 11/** 12 * Create "where" criteria for the SQL query ActiveRecord::find() function 13 * 14 * @package GO.base.db 15 * @version $Id: File.class.inc.php 7607 2011-06-15 09:17:42Z mschering $ 16 * @copyright Copyright Intermesh BV. 17 * @author Merijn Schering <mschering@intermesh.nl> 18 * @author Wesley Smits <wsmits@intermesh.nl> 19 */ 20 21namespace GO\Base\Db; 22 23 24class FindCriteria { 25 26 private $_condition=''; 27 28 private static $_paramCount = 0; 29 30 private $_paramPrefix = ':go'; 31 32 private $_params=array(); 33 34 private $_columns; 35 36 private $_ignoreUnknownColumns=false; 37 /** 38 * Get a new instance object of this class file 39 * 40 * @return FindCriteria 41 */ 42 public static function newInstance(){ 43 return new self; 44 } 45 46 /** 47 * Add a model to the criteria object so it can determine of which PDO type a column is. 48 * You can also give an alias with it. If not then the alias defaults to "t". 49 * 50 * @param ActiveRecord $model An ActiveRecord model. 51 * @param String $tableAlias The alias that this model needs to use. Default: 't'. 52 */ 53 public function addModel($model, $tableAlias='t'){ 54 $this->_columns[$tableAlias]=$model->getColumns(); 55 return $this; 56 } 57 58 59 /** 60 * Private function to add 'AND' or 'OR' to the current condition. 61 * 62 * @param Boolean $useAnd True for 'AND', false for 'OR'. 63 */ 64 private function _appendOperator($useAnd){ 65 if($this->_condition!='') 66 $this->_condition .= $useAnd ? ' AND' : ' OR'; 67 68 } 69 70 /** 71 * Prevent warnings on column types when the model is unknown 72 * 73 * @return FindCriteria 74 */ 75 public function ignoreUnknownColumns(){ 76 $this->_ignoreUnknownColumns=true; 77 78 return $this; 79 } 80 81 /** 82 * Private function to recognize the PDOTYPE(http://www.php.net/manual/en/pdo.constants.php) of the given fields. 83 * 84 * @param String $tableAlias The alias of the table in this SQL statement. 85 * @param String $field The field for where the PDOTYPE needs to be checked. 86 * @return int type The constant of the found PDO type . 87 */ 88 private function _getPdoType($tableAlias, $field){ 89 if(isset($this->_columns[$tableAlias][$field]['type'])) 90 $type = $this->_columns[$tableAlias][$field]['type']; 91 else{ 92 $type= PDO::PARAM_STR; 93 if(!$this->_ignoreUnknownColumns){ 94 //\GO::debug("WARNING: Could not find column type for $tableAlias. $field in FindCriteria. Using PDO::PARAM_STR. Do you need to use addModel?"); 95// $trace = debug_backtrace(); 96// for($i=0;$i<count($trace);$i++){ 97// \GO::debug($trace[$i]['class'].'::'.$trace[$i]['function']); 98// } 99 100 } 101 102 } 103 return $type; 104 } 105 106 /** 107 * Private function to add the given condition to the rest of this object's condition string. 108 * 109 * @param String $tableAlias The alias of the table in this SQL statement. 110 * @param String $field The field where this condition is for. 111 * @param Mixed $value The value of the field for this condition. 112 * @param String $comparator How needs this field be compared with the value. Can be ('<','>','<>','=<','>=','='). 113 */ 114 private function _appendConditionString($tableAlias, $field, $value, $comparator, $valueIsColumn){ 115 116 $this->_validateComparator($comparator); 117 118 if (is_null($value)) { 119 if ($comparator == "=") 120 $comparator = "IS"; 121 $paramTag = "NULL"; 122 }elseif(!$valueIsColumn){ 123 $paramTag = $this->_getParamTag(); 124 $this->_params[$paramTag]=array($value, $this->_getPdoType($tableAlias, $field)); 125 }else 126 { 127 $paramTag=$value; 128 } 129 130 $this->_condition .= ' `'.$tableAlias.'`.`'.$field.'` '.$comparator.' '.$paramTag; 131 } 132 133 134 private function _validateComparator($comparator){ 135 if(!preg_match("/[=!><a-z]/i", $comparator)) 136 throw new \Exception("Invalid comparator: ".$comparator); 137 } 138 139 /** 140 * Adds a condition to this object and returns itself. 141 * 142 * @param String $field The field where this condition is for. 143 * @param String $value The value of the field for this condition. 144 * @param String $comparator How needs this field be compared with the value. Can be ('<','>','<>','=<','>=','='). 145 * @param String $tableAlias The alias of the table for the $field parameter 146 * @param Boolean $useAnd True for 'AND', false for 'OR'. Default: true. 147 * @param Boolean $valueIsColumn Treat the value as a column name. In this case the value must contain the table alias too if necessary. 148 * @return FindCriteria The complete FindCriteria object is given as a return value. 149 */ 150 public function addCondition($field, $value, $comparator='=',$tableAlias='t', $useAnd=true, $valueIsColumn=false) { 151 152 if(!is_string($field)) 153 throw new \Exception("field parameter for addCondition should be a string"); 154 155 $this->_appendOperator($useAnd); 156 $this->_appendConditionString($tableAlias, $field, $value, $comparator, $valueIsColumn); 157 return $this; 158 } 159 160 /** 161 * Adds a condition to this object and returns itself. 162 * 163 * You can also create field IS NULL with this function for example. 164 * 165 * WARNING: This function does not do any sanity checks on the input! It just 166 * inserts the plain values so user input may not be passed to this function. 167 * You can use parameter tags like :paramName and use the addBindParameter function. 168 * 169 * 170 * @param String $value1 The field value where this condition is for. 171 * @param String $value The value of the field for this condition. 172 * @param String $comparator How needs this field be compared with the value. Can be ('<','>','<>','=<','>=','='). 173 * @param Boolean $useAnd True for 'AND', false for 'OR'. Default: true. 174 * @return FindCriteria The complete FindCriteria object is given as a return value. 175 */ 176 public function addRawCondition($value1, $value2=null, $comparator='=', $useAnd=true) { 177 $this->_appendOperator($useAnd); 178 if($value2===null) 179 $this->_condition .= ' '.$value1; 180 else 181 $this->_appendRawConditionString($value1, $value2, $comparator); 182 return $this; 183 } 184 185 /** 186 * Add a custom bind parameter. Only useful in combination with addRawCondition. 187 * 188 * @param StringHelper $paramTag eg. ":paramName" 189 * @param mixed $value 190 * @param int $pdoType 191 * @return FindCriteria The complete FindCriteria object is given as a return value. 192 */ 193 public function addBindParameter($paramTag, $value, $pdoType=PDO::PARAM_STR){ 194 $this->addParams(array($paramTag=>array($value, $pdoType))); 195 return $this; 196 } 197 198 /** 199 * Private function to add the given condition to the rest of this object's condition string. 200 * 201 * WARNING: This function does not do any sanity checks on the input! It just 202 * inserts the plain values so user input may not be passed to this function. 203 * 204 * @param String $value1 The raw field where this condition is for. 205 * @param Mixed $value2 The raw value of the field for this condition. 206 * @param String $comparator How needs this field be compared with the value. Can be ('<','>','<>','=<','>=','='). 207 */ 208 private function _appendRawConditionString($value1, $value2, $comparator) { 209 $this->_validateComparator($comparator); 210 $this->_condition .= ' '.$value1.' '.$comparator.' '.$value2; 211 } 212 213 /** 214 * Add an IN condition to this object and returns itself. 215 * 216 * @param String $field The field where this condition is for. 217 * @param Array $value The value of the field for this condition. 218 * @param String $tableAlias The alias of the table in this SQL statement. 219 * @param Boolean $useAnd True for 'AND', false for 'OR'. Default: true. 220 * @param Boolean $useNot True for 'NOT IN', false for 'IN'. Default: false. 221 * @return FindCriteria The complete FindCriteria object is given as a return value. 222 */ 223 public function addInCondition($field, $values, $tableAlias='t', $useAnd=true, $useNot=false) { 224 225 if(!is_array($values)) 226 throw new \Exception("ERROR: Value for addInCondition must be an array"); 227 228// if(!count($value)) 229// throw new \Exception("ERROR: Value for addInCondition can't be empty"); 230 231 if(!count($values)) 232 return $this; 233 234 $this->_appendOperator($useAnd); 235 $comparator = $useNot ? 'NOT IN' : 'IN'; 236 237 $paramTags=array(); 238 foreach($values as $val){ 239 $paramTag = $this->_getParamTag(); 240 $paramTags[]=$paramTag; 241 $this->_params[$paramTag]=array($val, $this->_getPdoType($tableAlias, $field)); 242 } 243 244 245 $this->_condition .= ' `'.$tableAlias.'`.`'.$field.'` '.$comparator.' ('.implode(',',$paramTags).')'; 246 247 return $this; 248 249 } 250 251 252 private static $_temporaryTables=array(); 253 /** 254 * IN conditions can be slow on large datasets. Creating a temporary table 255 * for the query can be much faster. 256 * This function does the same as addInCondition but uses a subselect in a temporary table. 257 * 258 * @param String $tableName The name of the temporary table 259 * @param String $field The field where this condition is for. 260 * @param Array $value The value of the field for this condition. 261 * @param String $tableAlias The alias of the table in this SQL statement. 262 * @param Boolean $useAnd True for 'AND', false for 'OR'. Default: true. 263 * @param Boolean $useNot True for 'NOT IN', false for 'IN'. Default: false. 264 * @return \FindCriteria 265 * @throws Exception 266 */ 267 public function addInTemporaryTableCondition($tableName, $field, $values, $tableAlias='t', $useAnd=true, $useNot=false){ 268 if(!is_array($values)) 269 throw new \Exception("ERROR: Value for addInCondition must be an array"); 270 271 if(!count($values)) 272 return $this; 273 274 $this->_createTemporaryTable($tableName, $values); 275 276 $this->addRawCondition($tableAlias.'.'.$field, '(SELECT id FROM `'.$tableName.'`)' , $useNot ? 'NOT IN' : 'IN', $useAnd); 277 278 return $this; 279 } 280 281 private function _createTemporaryTable($tableName, $values){ 282 if(!isset(self::$_temporaryTables[$tableName])){ 283 $sql = "CREATE TEMPORARY TABLE `$tableName` ( 284 `id` int(11) NOT NULL, 285 PRIMARY KEY (`id`) 286 ) ENGINE = MEMORY;"; 287 \GO::getDbConnection()->query($sql); 288 289 self::$_temporaryTables[$tableName]=true; 290 }else 291 { 292 \GO::getDbConnection()->query("TRUNCATE TABLE `$tableName`"); 293 } 294 295 $this->sleepingTempTables[$tableName]=$values; 296 297 298 $sql = "INSERT INTO `$tableName` (id) VALUES (".implode('),(', $values).")"; 299 \GO::getDbConnection()->query($sql); 300 } 301 302 private $sleepingTempTables=array(); 303 304 305 public function recreateTemporaryTables() { 306 307 //when this object is in session for export we need to recreate the temp tables. 308 309 foreach($this->sleepingTempTables as $tableName=>$values){ 310 $this->_createTemporaryTable($tableName,$values); 311 } 312 } 313 314 /** 315 * Add a fulltext search query 316 * 317 * @param StringHelper $field 318 * @param StringHelper $matchQuery 319 * @param StringHelper $tableAlias 320 * @param boolean $useAnd 321 * @param StringHelper $mode 322 * @return FindCriteria 323 */ 324 public function addMatchCondition($field, $matchQuery, $tableAlias='t', $useAnd=true, $mode='BOOLEAN'){ 325 $this->_appendOperator($useAnd); 326 327 $paramTag = $this->_getParamTag(); 328 $this->_params[$paramTag]=array($matchQuery, PDO::PARAM_STR); 329 330 $fields = array(); 331 if(!is_array($field)) 332 $field = array($field); 333 334 foreach($field as $f) 335 $fields[]='`'.$tableAlias.'`.`'.$f.'`'; 336 337 $this->_condition .= ' MATCH('.implode(",", $fields).') AGAINST ('.$paramTag; 338 339 if($mode!='NATURAL') 340 $this->_condition .= ' IN '.$mode.' MODE'; 341 342 $this->_condition .= ')'; 343 return $this; 344 } 345 346 /** 347 * Add a search condition to this object and returns itself. 348 * The $useExact parameter verifies the given value as an exact string or adds a '%' before and after the given value. 349 * 350 * @param String $field The field where this condition is for. 351 * @param String $value The value of the field for this condition. 352 * @param Boolean $useAnd True for 'AND', false for 'OR'. Default: true. 353 * @param Boolean $useNot True for 'NOT LIKE', false for 'LIKE'. Default: false. 354 * @param Boolean $partialMatch True if want to search on partials of the term. Default: false. 355 * @param String $tableAlias The table alias to apply this searchcondition to. 356 * 357 * @return FindCriteria The complete FindCriteria object is given as a return value. 358 */ 359 public function addSearchCondition($field, $value, $useAnd=true, $useNot=false, $partialMatch=false, $tableAlias = 't') { 360 361 $this->_appendOperator($useAnd); 362 363 $comparator = $useNot ? 'NOT LIKE' : 'LIKE'; 364 365 if($partialMatch) { 366 $value = '%' . preg_replace('/[\s*]+/', '%', $value) . '%'; 367 } 368 369 $this->_appendConditionString($tableAlias, $field, $value, $comparator, false); 370 371 return $this; 372 } 373 374 /** 375 * Private function to get the current parameter prefix. 376 * 377 * @return String The next available parameter prefix. 378 */ 379 private function _getParamTag() { 380 self::$_paramCount++; 381 return $this->_paramPrefix.self::$_paramCount; 382 } 383 384 /** 385 * Returns the current condition value of this FindCriteria object as a string. 386 * 387 * @return String Current condition value. 388 */ 389 public function getCondition() { 390 return $this->_condition; 391 } 392 393 /** 394 * Returns the current parameter values of this FindCriteria object as an array. 395 * 396 * @return Array Current parameter values. 397 */ 398 public function getParams() { 399 return $this->_params; 400 } 401 402 /** 403 * Merge an other FindCriteria object together with this FindCriteria object. 404 * Then returns the complete merged FindCriteria object. 405 * 406 * @param FindCriteria $criteria The FindCriteria object that needs to be merged with this FindCriteria object. 407 * @param Boolean $useAnd True for 'AND', false for 'OR'. Default: true. 408 * @return FindCriteria The complete FindCriteria object is given as a return value. 409 */ 410 public function mergeWith(FindCriteria $criteria, $useAnd=true) { 411 412 $condition = $criteria->getCondition(); 413 414 if(!empty($condition)){ 415 $operator = $useAnd ? 'AND' : 'OR'; 416 417 $thisCondition = $this->getCondition(); 418 if(!empty($thisCondition)) 419 { 420 $this->_condition = ' ('.$thisCondition.') '.$operator.' ('.$condition .')'; 421 422 }else 423 { 424 $this->_condition = '('.$condition.')'; 425 } 426 } 427 //always merge params. FindParams::join can add params without a condtion. 428 $this->_params = array_merge($this->getParams(), $criteria->getParams()); 429 return $this; 430 } 431 432 /** 433 * Add extra params to bind to the query. This is used by FindParams::join() 434 * 435 * @var array $params 436 */ 437 public function addParams($params){ 438 $this->_params = array_merge($this->getParams(), $params); 439 } 440} 441