TYPO3CMS  8
 All Classes Namespaces Files Functions Variables Pages
ExpressionBuilder.php
Go to the documentation of this file.
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Database\Query\Expression;
4 
5 /*
6  * This file is part of the TYPO3 CMS project.
7  *
8  * It is free software; you can redistribute it and/or modify it under
9  * the terms of the GNU General Public License, either version 2
10  * of the License, or any later version.
11  *
12  * For the full copyright and license information, please read the
13  * LICENSE.txt file that was distributed with this source code.
14  *
15  * The TYPO3 project - inspiring people to share!
16  */
17 
19 
32 {
33  const EQ = '=';
34  const NEQ = '<>';
35  const LT = '<';
36  const LTE = '<=';
37  const GT = '>';
38  const GTE = '>=';
39 
40  const QUOTE_NOTHING = 0;
41  const QUOTE_IDENTIFIER = 1;
42  const QUOTE_PARAMETER = 2;
43 
49  protected $connection;
50 
57  {
58  $this->connection = $connection;
59  }
60 
68  public function andX(...$expressions): CompositeExpression
69  {
70  return new CompositeExpression(CompositeExpression::TYPE_AND, $expressions);
71  }
72 
80  public function orX(...$expressions): CompositeExpression
81  {
82  return new CompositeExpression(CompositeExpression::TYPE_OR, $expressions);
83  }
84 
94  public function comparison($leftExpression, string $operator, $rightExpression): string
95  {
96  return $leftExpression . ' ' . $operator . ' ' . $rightExpression;
97  }
98 
107  public function eq(string $fieldName, $value): string
108  {
109  return $this->comparison($this->connection->quoteIdentifier($fieldName), static::EQ, $value);
110  }
111 
126  public function neq(string $fieldName, $value): string
127  {
128  return $this->comparison($this->connection->quoteIdentifier($fieldName), static::NEQ, $value);
129  }
130 
139  public function lt($fieldName, $value): string
140  {
141  return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LT, $value);
142  }
143 
152  public function lte(string $fieldName, $value): string
153  {
154  return $this->comparison($this->connection->quoteIdentifier($fieldName), static::LTE, $value);
155  }
156 
165  public function gt(string $fieldName, $value): string
166  {
167  return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GT, $value);
168  }
169 
178  public function gte(string $fieldName, $value): string
179  {
180  return $this->comparison($this->connection->quoteIdentifier($fieldName), static::GTE, $value);
181  }
182 
190  public function isNull(string $fieldName): string
191  {
192  return $this->connection->quoteIdentifier($fieldName) . ' IS NULL';
193  }
194 
202  public function isNotNull(string $fieldName): string
203  {
204  return $this->connection->quoteIdentifier($fieldName) . ' IS NOT NULL';
205  }
206 
215  public function like(string $fieldName, $value): string
216  {
217  return $this->comparison($this->connection->quoteIdentifier($fieldName), 'LIKE', $value);
218  }
219 
228  public function notLike(string $fieldName, $value): string
229  {
230  return $this->comparison($this->connection->quoteIdentifier($fieldName), 'NOT LIKE', $value);
231  }
232 
242  public function in(string $fieldName, $value): string
243  {
244  return $this->comparison(
245  $this->connection->quoteIdentifier($fieldName),
246  'IN',
247  '(' . implode(', ', (array)$value) . ')'
248  );
249  }
250 
260  public function notIn(string $fieldName, $value): string
261  {
262  return $this->comparison(
263  $this->connection->quoteIdentifier($fieldName),
264  'NOT IN',
265  '(' . implode(', ', (array)$value) . ')'
266  );
267  }
268 
277  public function inSet(string $fieldName, string $value): string
278  {
279  if ($value === '') {
280  throw new \InvalidArgumentException(
281  'ExpressionBuilder::inSet() can not be used with an empty string value.',
282  1459696089
283  );
284  }
285 
286  if (strpos($value, ',') !== false) {
287  throw new \InvalidArgumentException(
288  'ExpressionBuilder::inSet() can not be used with values that contain a comma (",").',
289  1459696090
290  );
291  }
292 
293  switch ($this->connection->getDatabasePlatform()->getName()) {
294  case 'postgresql':
295  case 'pdo_postgresql':
296  return $this->comparison(
297  $this->literal($value),
298  self::EQ,
299  sprintf(
300  'ANY(string_to_array(%s, %s))',
301  $this->connection->quoteIdentifier($fieldName) . '::text',
302  $this->literal(',')
303  )
304  );
305  break;
306  case 'oci8':
307  case 'pdo_oracle':
308  throw new \RuntimeException(
309  'FIND_IN_SET support for database platform "Oracle" not yet implemented.',
310  1459696680
311  );
312  break;
313  case 'sqlsrv':
314  case 'pdo_sqlsrv':
315  throw new \RuntimeException(
316  'FIND_IN_SET support for database platform "SQLServer" not yet implemented.',
317  1459696681
318  );
319  break;
320  case 'sqlite':
321  case 'sqlite3':
322  case 'pdo_sqlite':
323  if (strpos($value, ':') === 0 || $value === '?') {
324  throw new \InvalidArgumentException(
325  'ExpressionBuilder::inSet() for SQLite can not be used with placeholder arguments.',
326  1476029421
327  );
328  }
329 
330  return $this->comparison(
331  implode('||', [
332  $this->literal(','),
333  $this->connection->quoteIdentifier($fieldName),
334  $this->literal(','),
335  ]),
336  'LIKE',
337  $this->literal(
338  '%,' . $this->unquoteLiteral($value) . ',%'
339  )
340  );
341  break;
342  default:
343  return sprintf(
344  'FIND_IN_SET(%s, %s)',
345  $value,
346  $this->connection->quoteIdentifier($fieldName)
347  );
348  }
349  }
350 
358  public function bitAnd(string $fieldName, int $value): string
359  {
360  switch ($this->connection->getDatabasePlatform()->getName()) {
361  case 'oci8':
362  case 'pdo_oracle':
363  return sprintf(
364  'BITAND(%s, %s)',
365  $this->connection->quoteIdentifier($fieldName),
366  $value
367  );
368  default:
369  return $this->comparison(
370  $this->connection->quoteIdentifier($fieldName),
371  '&',
372  $value
373  );
374  }
375  }
376 
384  public function min(string $fieldName, string $alias = null): string
385  {
386  return $this->calculation('MIN', $fieldName, $alias);
387  }
388 
396  public function max(string $fieldName, string $alias = null): string
397  {
398  return $this->calculation('MAX', $fieldName, $alias);
399  }
400 
408  public function avg(string $fieldName, string $alias = null): string
409  {
410  return $this->calculation('AVG', $fieldName, $alias);
411  }
412 
420  public function sum(string $fieldName, string $alias = null): string
421  {
422  return $this->calculation('SUM', $fieldName, $alias);
423  }
424 
432  public function count(string $fieldName, string $alias = null): string
433  {
434  return $this->calculation('COUNT', $fieldName, $alias);
435  }
436 
445  protected function calculation(string $aggregateName, string $fieldName, string $alias = null): string
446  {
447  $aggregateSQL = sprintf(
448  '%s(%s)',
449  $aggregateName,
450  $this->connection->quoteIdentifier($fieldName)
451  );
452 
453  if (!empty($alias)) {
454  $aggregateSQL .= ' AS ' . $this->connection->quoteIdentifier($alias);
455  }
456 
457  return $aggregateSQL;
458  }
459 
468  public function literal($input, string $type = null): string
469  {
470  return $this->connection->quote($input, $type);
471  }
472 
479  protected function unquoteLiteral(string $value): string
480  {
481  $quoteChar = $this->connection
482  ->getDatabasePlatform()
483  ->getStringLiteralQuoteCharacter();
484 
485  $isQuoted = strpos($value, $quoteChar) === 0 && strpos(strrev($value), $quoteChar) === 0;
486 
487  if ($isQuoted) {
488  return str_replace($quoteChar . $quoteChar, $quoteChar, substr($value, 1, -1));
489  }
490 
491  return $value;
492  }
493 }
comparison($leftExpression, string $operator, $rightExpression)
calculation(string $aggregateName, string $fieldName, string $alias=null)