Expression builder¶

Table of Contents

Introduction
Basic usage
Junctions
Comparisons
Aggregate Functions
Various Expressions
- trim()
- length()

Introduction ¶

The \TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder class is responsible for dynamically creating 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 and ensures that table and column names are quoted within the created expressions / SQL fragments. It is a facade to the actual Doctrine DBAL ExpressionBuilder.

The expression builder is used in the context of the query builder to ensure that queries are built based on the requirements of the database platform being used.

Basic usage ¶

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

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

$expressionBuilder = $queryBuilder->expr();

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

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';

    public function __construct(
        private readonly ConnectionPool $connectionPool
    ) {}

    public function findSomething()
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);

        $rows = $queryBuilder
            ->select('uid', 'header', 'bodytext')
            ->from(self::TABLE_NAME)
            ->where(
                // `bodytext` = 'lorem' AND `header` = 'dolor'
                $queryBuilder->expr()->eq(
                    'bodytext',
                    $queryBuilder->createNamedParameter('lorem')
                ),
                $queryBuilder->expr()->eq(
                    'header',
                    $queryBuilder->createNamedParameter('dolor')
                )
            )
            ->executeQuery()
            ->fetchAllAssociative();

        // ...
    }
}

Warning

It is of crucial importance to quote values correctly to not introduce SQL injection attack vectors into your application. See the according section of the query builder for details.

Junctions ¶

Changed in version 11.5.10: The andX() and orX() methods are deprecated and replaced by and() and or() to match with Doctrine DBAL, which deprecated these methods.

->and() conjunction
->or() disjunction

Combine multiple single expressions with AND or OR. Nesting is possible, both methods are variadic and accept any number of arguments, which are all combined. However, it usually makes little sense to pass zero or only one argument.

Example to find tt_content records:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

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

Read how to correctly instantiate a query builder with the connection pool.

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
->notInSet($fieldName, $value) "NOT FIND_IN_SET('42', aField)" Find a value not 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().

Warning

Failing to quote 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:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

// `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 'lorem'
->like(
    'bodytext',
    $queryBuilder->createNamedParameter(
        $queryBuilder->escapeLikeWildcards('lorem')
    )
)

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

// usergroup does not contain 42
->notInSet('usergroup', $queryBuilder->createNamedParameter('42'))

// 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. The first argument is the field name (or table name / alias with field name), the second argument is 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:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

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

// 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')
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Various Expressions ¶

trim()¶

Using the ->trim() expression ensures that the fields are trimmed at the database level. The following examples give a better idea of what is possible:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

// use TYPO3\CMS\Core\Database\Connection
// use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->trim($fieldName),
    ExpressionBuilder::EQ,
    $queryBuilder->createNamedParameter('', Connection::PARAM_STR)
);

Read how to correctly instantiate a query builder with the connection pool.

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. The signature of the method signature is $fieldName with an optional alias ->length(string $fieldName, string $alias = null):

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php¶

// use TYPO3\CMS\Core\Database\Connection;
// use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->length($fieldName),
    ExpressionBuilder::GT,
    $queryBuilder->createNamedParameter(0, Connection::PARAM_INT)
);

Read how to correctly instantiate a query builder with the connection pool.