Attention

TYPO3 v10 has reached end-of-life as of April 30th 2023 and is no longer being maintained. Use the version switcher on the top left of this page to select documentation for a supported version of TYPO3.

Need more time before upgrading? You can purchase Extended Long Term Support (ELTS) for TYPO3 v10 here: TYPO3 ELTS.

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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
$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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// WHERE
//     (`tt_content`.`CType` = 'list')
//     AND (
//        (`tt_content`.`list_type` = 'example_pi1')
//        OR
//        (`tt_content`.`list_type` = 'example_pi2')
//     )
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$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, $value) "LIKE" comparison

  • ->notLike($fieldName, $value) "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:

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') . '%')
)

// use TYPO3\CMS\Core\Database\Connection;
// `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
   )
)

// use TYPO3\CMS\Core\Database\Connection;
// `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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// Calculate the average creation timestamp of all rows from tt_content
// SELECT AVG(`crdate`) AS `averagecreation` FROM `tt_content`
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('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:

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$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):

// use TYPO3\CMS\Core\Utility\GeneralUtility;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');
$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->length($fieldName),
    ExpressionBuilder::GT,
    $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
);