Attention

TYPO3 v8 has reached its end-of-life March 31st, 2020 and is not maintained by the community anymore. Looking for a stable version? Use the version switch on the top left.

There is no further ELTS support. It is recommended that you upgrade your project and use a supported version of TYPO3.

ExpressionBuilder¶

The ExpressionBuilder class is responsible to dynamically create SQL query parts for WHERE and JOIN ON conditions, functions like ->min() may also be used in SELECT parts.

It takes care of building query conditions while ensuring table and column names are quoted within the created expressions / SQL fragments. It is a facade to the actual doctrine-dbal ExpressionBuilder.

The ExpressionBuilder is used within the context of the QueryBuilder to ensure queries are being build based on the requirements of the database platform in use.

An instance of the ExpressionBuilder is retrieved from the QueryBuilder object:

$expressionBuilder = $queryBuilder->expr();

It is good practice to not assign an instance of the ExpressionBuilder to a variable but to use it within the code flow of the QueryBuilder context directly:

$rows = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content')
   ->select('uid', 'header', 'bodytext')
   ->from('tt_content')
   ->where(
      // `bodytext` = 'klaus' AND `header` = 'peter'
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus')),
      $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('peter'))
   )
   ->execute()
   ->fetchAll();

Warning

It is crucially important to quote values correctly to not introduce SQL injection attack vectors to your application. See the section of the QueryBuilder for details.

Junctions¶

->andX() conjunction
->orX() disjunction

Combine multiple single expressions with AND or OR. Nesting is possible, both methods are variadic and take any number of argument which are all combined. It usually doesn't make much sense to hand over zero or only one argument, though.

Example to find tt_content records:

// WHERE
//     (`tt_content`.`CType` = 'list')
//     AND (
//        (`tt_content`.`list_type` = 'example_pi1')
//        OR
//        (`tt_content`.`list_type` = 'example_pi2')
//     )
$queryBuilder->where(
   $queryBuilder->expr()->eq('CType', $queryBuilder->createNamedParameter('list')),
   $queryBuilder->expr()->orX(
      $queryBuilder->expr()->eq('list_type', $queryBuilder->createNamedParameter('example_pi1')),
      $queryBuilder->expr()->eq('list_type', $queryBuilder->createNamedParameter('example_pi2'))
   )
)

Comparisons¶

A set of methods to create various comparison expressions or SQL functions:

->eq($fieldName, $value) "equal" comparison =
->neq($fieldName, $value) "not equal" comparison !=
->lt($fieldName, $value) "less than" comparison <
->lte($fieldName, $value) "less than or equal" comparison <=
->gt($fieldName, $value) "greater than" comparison >
->gte($fieldName, $value) "greater than or equal" comparison >=
->isNull($fieldName) "IS NULL" comparison
->isNotNull($fieldName) "IS NOT NULL" comparison
->like($fieldName) "LIKE" comparison
->notLike($fieldName) "NOT LIKE" comparison
->in($fieldName, $valueArray) "IN ()" comparison
->notIn($fieldName, $valueArray) "NOT IN ()" comparison
->inSet($fieldName, $value) "FIND_IN_SET('42', aField)" Find a value in a comma separated list of values
->bitAnd($fieldName, $value) A bitwise AND operation &

Remarks and warnings:

The first argument $fieldName is always quoted automatically.
All methods that have a $value or $valueList as second argument must be quoted, usually by calling $queryBuilder->createNamedParameter() or $queryBuilder->quoteIdentifier(). Failing to do so will end up in SQL injections!
->like() and ->notLike() values must be additionally quoted with a call to $queryBuilder->escapeLikeWildcards($value) to suppress the special meaning of % characters from $value.

Examples:

// `bodytext` = 'foo' - string comparison
->eq('bodytext', $queryBuilder->createNamedParameter('foo'))

// `tt_content`.`bodytext` = 'foo'
->eq('tt_content.bodytext', $queryBuilder->createNamedParameter('foo'))

// `aTableAlias`.`bodytext` = 'foo'
->eq('aTableAlias.bodytext', $queryBuilder->createNamedParameter('foo'))

// `uid` = 42 - integer comparison
->eq('uid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))

// `uid` >= 42
->gte('uid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))

// `bodytext` LIKE 'klaus'
->like(
   'bodytext',
   $queryBuilder->createNamedParameter($queryBuilder->escapeLikeWildcards('klaus'))
)

// `bodytext` LIKE '%klaus%'
->like(
   'bodytext',
   $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards('klaus') . '%')
)

// `uid` IN (42, 0, 44) - properly sanitized, mind the intExplode and PARAM_INT_ARRAY
->in(
   'uid',
   $queryBuilder->createNamedParameter(
      GeneralUtility::intExplode(',', '42, karl, 44', true),
      Connection::PARAM_INT_ARRAY
   )
)

// `CType` IN ('media', 'multimedia') - properly sanitized, mind the PARAM_STR_ARRAY
->in(
   'CType',
   $queryBuilder->createNamedParameter(
      ['media', 'multimedia'],
      Connection::PARAM_STR_ARRAY
   )
)

Aggregate functions¶

Aggregate functions used in SELECT parts, often combined with GROUP BY. First argument is the field name (or table name / alias with field name), second argument an optional alias.

->min($fieldName, $alias = NULL) "MIN()" calculation
->max($fieldName, $alias = NULL) "MAX()" calculation
->avg($fieldName, $alias = NULL) "AVG()" calculation
->sum($fieldName, $alias = NULL) "SUM()" calculation
->count($fieldName, $alias = NULL) "COUNT()" calculation

Examples:

// Calculate the average creation timestamp of all rows from tt_content
// SELECT AVG(`crdate`) AS `averagecreation` FROM `tt_content`
$result = $queryBuilder
   ->addSelectLiteral(
      $queryBuilder->expr()->avg('crdate', 'averagecreation')
   )
   ->from('tt_content')
   ->execute()
   ->fetch();

// Distinct list of all existing endtime values from tt_content
// SELECT `uid`, MAX(`endtime`) AS `maxendtime` FROM `tt_content` GROUP BY `endtime`
$statement = $queryBuilder
   ->select('uid')
   ->addSelectLiteral(
      $queryBuilder->expr()->max('endtime', 'maxendtime')
   )
   ->from('tt_content')
   ->groupBy('endtime')
   ->execute();

Various Expressions¶

TRIM¶

Using the TRIM expression makes sure fields get trimmed on database level. See the examples below to get a better idea of what can be done.

$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->trim($fieldName),
    ExpressionBuilder::EQ,
    $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
);

The call to $queryBuilder->expr()-trim() can be one of the following:

trim('fieldName') results in TRIM("tableName"."fieldName")
trim('fieldName', AbstractPlatform::TRIM_LEADING, 'x') results in TRIM(LEADING "x" FROM "tableName"."fieldName")
trim('fieldName', AbstractPlatform::TRIM_TRAILING, 'x') results in TRIM(TRAILING "x" FROM "tableName"."fieldName")
trim('fieldName', AbstractPlatform::TRIM_BOTH, 'x') results in TRIM(BOTH "x" FROM "tableName"."fieldName")

LENGTH¶

The LENGTH string function can be used to return the length of a string in bytes, method signature is fieldName with optional alias ->length(string $fieldName, string $alias = null)

$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->length($fieldName),
    ExpressionBuilder::GT,
    $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
);