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