1<?php 2namespace go\core\db; 3 4use Exception; 5 6/** 7 * Create "where", "having" or "join on" part of the query for {@see \go\core\db\Query} 8 * 9 * @copyright (c) 2014, Intermesh BV http://www.intermesh.nl 10 * @author Merijn Schering <mschering@intermesh.nl> 11 * @license http://www.gnu.org/licenses/agpl-3.0.html AGPLv3 12 */ 13class Criteria { 14 15 protected $where = []; 16 17 /** 18 * Key value array of bind parameters. 19 * 20 * @var array eg. ['paramTag' => ':someTag', 'value' => 'Some value', 'pdoType' => PDO::PARAM_STR] 21 */ 22 protected $bindParameters = []; 23 24 /** 25 * Creates a new Criteria or Query object from different input: 26 * 27 * * null => new Criteria(); 28 * * Array: ['key'= > value] = (new Criteria())->where(['key'= > value]); 29 * * String: "col=:val" = (new Criteria())->where("col=:val"); 30 * * A Query object is returned as is. 31 * 32 * @param array|string|static $criteria 33 * @return static 34 * @throws Exception 35 */ 36 public static function normalize($criteria = null) { 37 if (!isset($criteria)) { 38 return new static; 39 } 40 41 if($criteria instanceof static) { 42 return $criteria; 43 } 44 45 if(is_object($criteria)) { 46 throw new Exception("Invalid query object passed: ".get_class($criteria).". Should be an go\core\orm\Query object, array or string."); 47 } 48 49 return (new static)->where($criteria); 50 } 51 52 /** 53 * The where conditions 54 * 55 * Use {@see where()} to add new. 56 * 57 * @return array 58 */ 59 public function getWhere() { 60 return $this->where; 61 } 62 63 /** 64 * Key value array of bind parameters. 65 * 66 * @return array eg. ['paramTag' => ':someTag', 'value' => 'Some value', 'pdoType' => PDO::PARAM_STR] 67 */ 68 public function getBindParameters() { 69 return $this->bindParameters; 70 } 71 72 /** 73 * Set where parameters. 74 * 75 * Basic usage 76 * =========== 77 * 78 * There are 3 ways to use this function: 79 * 80 * 1. Specify column, operator and value. 81 * 82 * ``` 83 * $query = (new Query()) 84 * ->select('*') 85 * ->from('test_a') 86 * ->where('id', '=', 1) 87 * 88 * ``` 89 * 90 * 2. Provide a key value array with column name value. 91 * ``` 92 * $query = (new Query()) 93 * ->select('*') 94 * ->from('test_a') 95 * ->where(['id' => 1, 'name' => 'merijn']); //WHERE id=1 and name='merijn' 96 * ``` 97 * 98 * 3. Provide a raw string. 99 * Note that you MUST use {@see bind()} for binding values to prevent SQL 100 * injection. Do not concatenate values. 101 * 102 * ``` 103 * $query = (new Query()) 104 * ->select('*') 105 * ->from('test_a') 106 * ->where('id = :id') 107 * ->bind(':id', 1); 108 * ``` 109 * 110 * Parameter grouping 111 * ================== 112 * 113 * You can group parameters by passing another Criteria object: 114 * 115 * ``` 116 * $query = (new Query()) 117 * ->select('*') 118 * ->from('test_a') 119 * ->where('id', '=', 1) 120 * ->andWhere( 121 * (new Criteria()) 122 * ->where("id", "=", 2) 123 * ->orWhere("id", '>', 1) 124 * ); 125 * ``` 126 * 127 * Sub queries 128 * =========== 129 * 130 * The query builder also handles sub queries. 131 * 132 * An IN sub query: 133 * ``` 134 * $query = (new Query()) 135 * ->select('*') 136 * ->from('test_a', "a") 137 * ->join("test_b", "b", "a.id = b.id") 138 * ->where('id', 'IN', 139 * (new Query) 140 * ->select('id') 141 * ->from("test_b", 'sub_b') 142 * ); 143 * ```` 144 * 145 * An EXISTS sub query: 146 * 147 * ``` 148 * $query = (new Query()) 149 * ->select('*') 150 * ->from('test_a', "a") 151 * ->whereExists( 152 * (new Query) 153 * ->select('id') 154 * ->from("test_b", 'sub_b') 155 * ->where("sub_b.id = a.id") 156 * ); 157 * ``` 158 * 159 * @param string|array|Criteria $condition 160 * @param string $comparisonOperator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 161 * @param mixed $value 162 * 163 * @return static 164 */ 165 public function where($condition, $comparisonOperator = null, $value = null) { 166 return $this->andWhere($condition, $comparisonOperator, $value); 167 } 168 169 protected function internalWhere($condition, $comparisonOperator, $value, $logicalOperator) { 170 171 if(is_array($condition)) { 172 $count = count($condition); 173 if($count > 1) { 174 $sub = new Criteria(); 175 foreach($condition as $colName => $value) { 176 $op = is_array($value) || $value instanceof Query ? 'IN' : '='; 177 $sub->andWhere($colName, $op, $value); 178 } 179 $condition = $sub; 180 } else if ($count === 1) { 181 reset($condition); 182 $value = current($condition); 183 184 //Use "IN" for array values and sub queries 185 $op = is_array($value) || $value instanceof Query ? 'IN' : '='; 186 return ["column", $logicalOperator, key($condition), $op, $value]; 187 } 188 } 189 190 if(!isset($comparisonOperator) && (is_string($condition) || $condition instanceof Criteria)) { 191 //condition is raw string 192 return ["tokens", $logicalOperator, $condition]; 193 } 194 195 if(!isset($comparisonOperator)) { 196 $comparisonOperator = '='; 197 } 198 return ["column", $logicalOperator, $condition, $comparisonOperator, $value]; 199 200 } 201 202 protected function internalWhereExists(Query $subQuery, $not = false, $logicalOperator = "AND") { 203 $this->where[] = ["tokens", $logicalOperator, $not ? "NOT EXISTS" : "EXISTS", $subQuery]; 204 return $this; 205 } 206 207 public function whereExists(Query $subQuery, $not = false) { 208 return $this->andWhereExists($subQuery, $not); 209 } 210 211 public function andWhereExists(Query $subQuery, $not = false) { 212 return $this->internalWhereExists($subQuery, $not); 213 } 214 215 public function orWhereExists(Query $subQuery, $not = false) { 216 return $this->internalWhereExists($subQuery, $not , "OR"); 217 } 218 219 /** 220 * Add where condition with AND (..) 221 * 222 * {@see where()} 223 * 224 * @param String|array|Criteria $column 225 * @param string $comparisonOperator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 226 * @param mixed $value 227 * @return $this 228 */ 229 public function andWhere($column, $operator = null, $value = null) { 230 $this->where[] = $this->internalWhere($column, $operator, $value, 'AND'); 231 return $this; 232 } 233 234 /** 235 * Add where condition with AND NOT(..) 236 * 237 * Don't use this for ..WHERE a NO IN (SELECT... Just use 238 * 239 * andWhere('a', 'NOT IN', $query); 240 * 241 * {@see where()} 242 * 243 * @param String|array|Criteria $column 244 * @param string $operator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 245 * @param mixed $value 246 * @return $this 247 */ 248 public function andWhereNot($column, $operator = null, $value = null) { 249 $this->where[] = $this->internalWhere($column, $operator, $value, 'AND NOT'); 250 return $this; 251 } 252 253 /** 254 * Add where condition with AND NOT IFNULL(.., false)) 255 * 256 * WHERE NOT does not match NULL values. This is often not wanted so you can use this to wrap IFNULL so null values. 257 * 258 * For example: 259 * 260 * select * from contact left join address where NOT (address.country LIKE 'netherlands'); 261 * 262 * will not return contacts without an address. With this function it will do: 263 * 264 * select * from contact left join address where NOT IFNULL(address.country NOT LIKE 'netherlands', false); 265 * 266 * {@see where()} 267 * 268 * @param String|array|Criteria $column 269 * @param string $operator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 270 * @param mixed $value 271 * @return $this 272 */ 273 public function andWhereNotOrNull($column, $operator = null, $value = null) { 274 //NOT_OR_NULL will wrap an IFNULL(..., false) around it so it will also match NULL values 275 $this->where[] = $this->internalWhere($column, $operator, $value, 'AND NOT_OR_NULL'); 276 return $this; 277 } 278 279 /** 280 * Add where condition with OR NOT(..) 281 * 282 * {@see where()} 283 * 284 * @param String|array|Criteria $column 285 * @param string $operator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 286 * @param mixed $value 287 * @return $this 288 */ 289 public function orWhereNot($column, $operator = null, $value = null) { 290 $this->where[] = $this->internalWhere($column, $operator, $value, 'OR NOT'); 291 return $this; 292 } 293 294 /** 295 * Add where condition with OR NOT IFNULL(.., false)) 296 * 297 * WHERE NOT does not match NULL values. This is often not wanted so you can use this to wrap IFNULL so null values. 298 * 299 * For example: 300 * 301 * select * from contact left join address where NOT (address.country LIKE 'netherlands'); 302 * 303 * will not return contacts without an address. With this function it will do: 304 * 305 * select * from contact left join address where NOT IFNULL(address.country NOT LIKE 'netherlands', false); 306 * 307 * {@see where()} 308 * 309 * @param String|array|Criteria $column 310 * @param string $operator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 311 * @param mixed $value 312 * @return $this 313 */ 314 public function orWhereNotOrNull($column, $operator = null, $value = null) { 315 $this->where[] = $this->internalWhere($column, $operator, $value, 'OR NOT_OR_NULL'); 316 return $this; 317 } 318 319 /** 320 * Concatenate where condition with OR 321 * 322 * {@see where()} 323 * 324 * @param String|array|Criteria $column 325 * @param string $operator =, !=, IN, NOT IN etc. Defaults to '=' OR 'IN' (for arrays) 326 * @param mixed $value 327 * @return static 328 */ 329 public function orWhere($column, $operator = null, $value = null) { 330 $this->where[] = $this->internalWhere($column, $operator, $value, 'OR'); 331 return $this; 332 } 333 334 335 /** 336 * Clear where conditions 337 * 338 * @return self 339 */ 340 public function clearWhere() { 341 $this->where = []; 342 343 return $this; 344 } 345 346 /** 347 * Add a parameter to bind to the SQL query 348 * 349 * ``````````````````````````````````````````````````````````````````````````` 350 * $query->where("userId = :userId") 351 * ->bind(':userId', $userId, \PDO::PARAM_INT); 352 * ``````````````````````````````````````````````````````````````````````````` 353 * 354 * OR as array: 355 * 356 * ``````````````````````````````````````````````````````````````````````````` 357 * $query->where("name = :name1 OR name = :name2") 358 * ->bind([':name1' => 'Pete', ':name2' => 'John']); 359 * ``````````````````````````````````````````````````````````````````````````` 360 * 361 * @param string|array $tag eg. ":userId" or [':userId' => 1] 362 * @param mixed $value 363 * @param int $pdoType {@see \PDO} Autodetected based on the type of $value if omitted. 364 * @return static 365 */ 366 public function bind($tag, $value = null, $pdoType = null) { 367 368 if(is_array($tag)) { 369 foreach($tag as $key => $value) { 370 $this->bind($key, $value); 371 } 372 return $this; 373 } 374 375 if (!isset($pdoType)) { 376 $pdoType = Utils::getPdoParamType($value); 377 } 378 379 $this->bindParameters[] = ['paramTag' => $tag, 'value' => $value, 'pdoType' => $pdoType]; 380 381 return $this; 382 } 383 384 public static $bindTag = 0; 385 386 /** 387 * Generate unique tag to use in {@see bind()} 388 * @return string 389 */ 390 public function bindTag() { 391 return 'qp' . self::$bindTag++; 392 } 393 394 /** 395 * Check if the criteria object holds conditions 396 * 397 * @return bool 398 */ 399 public function hasConditions() { 400 return !empty($this->where); 401 } 402} 403