Expression builder

Table of Contents

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder

ExpressionBuilder class is responsible to dynamically create SQL query parts.

It takes care 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 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.

Basic usage

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

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php 
$expressionBuilder = $queryBuilder->expr();
Copied!

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\Connection;
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', Connection::PARAM_STR),
                ),
                $queryBuilder->expr()->eq(
                    'header',
                    $queryBuilder->createNamedParameter('dolor', Connection::PARAM_STR),
                ),
            )
            ->executeQuery()
            ->fetchAllAssociative();

        // ...
    }
}
Copied!

See available parameter types.

Junctions

Changed in version 11.5.10

The andX() and orX() methods are replaced by and() and or() to match with Doctrine DBAL, which deprecated these methods. Both methods have been removed with TYPO3 v13.0.

  • ->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 
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function findSomething(): QueryBuilder
    {
        // WHERE
        //     (`tt_content`.`CType` = 'header')
        //     AND (
        //        (`tt_content`.`header_position` = 'center')
        //        OR
        //        (`tt_content`.`header_position` = 'right')
        //     )
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $queryBuilder->where(
            $queryBuilder->expr()->eq('CType', $queryBuilder->createNamedParameter('header')),
            $queryBuilder->expr()->or(
                $queryBuilder->expr()->eq(
                    'header_position',
                    $queryBuilder->createNamedParameter('center', Connection::PARAM_STR),
                ),
                $queryBuilder->expr()->eq(
                    'header_position',
                    $queryBuilder->createNamedParameter('right', Connection::PARAM_STR),
                ),
            ),
        );
        return $queryBuilder;
    }
}
Copied!

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

Comparisons

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

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
eq ( string $fieldName, ?mixed $value)

Creates an equality comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The value. No automatic quoting/escaping is done.

Returns
string
neq ( string $fieldName, ?mixed $value)

Creates a non equality comparison expression with the given arguments.

First argument is considered the left expression and the second is the right expression. When converted to string, it will generate a <left expr> <> <right expr>. Example:

[php]
// u.id <> 1
$q->where($q->expr()->neq('u.id', '1'));
Copied!
param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The value. No automatic quoting/escaping is done.

Returns
string
lt ( string $fieldName, ?mixed $value)

Creates a lower-than comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The value. No automatic quoting/escaping is done.

Returns
string
lte ( string $fieldName, ?mixed $value)

Creates a lower-than-equal comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The value. No automatic quoting/escaping is done.

Returns
string
gt ( string $fieldName, ?mixed $value)

Creates a greater-than comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The value. No automatic quoting/escaping is done.

Returns
string
gte ( string $fieldName, ?mixed $value)

Creates a greater-than-equal comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The value. No automatic quoting/escaping is done.

Returns
string
isNull ( string $fieldName)

Creates an IS NULL expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

Returns
string
isNotNull ( string $fieldName)

Creates an IS NOT NULL expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

Returns
string
like ( string $fieldName, ?mixed $value, ?string $escapeChar = NULL)

Creates a LIKE() comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

Argument to be used in LIKE() comparison. No automatic quoting/escaping is done.

param $escapeChar

the escapeChar, default: NULL

Returns
string
notLike ( string $fieldName, ?mixed $value, ?string $escapeChar = NULL)

Creates a NOT LIKE() comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

Argument to be used in NOT LIKE() comparison. No automatic quoting/escaping is done.

param $escapeChar

the escapeChar, default: NULL

Returns
string
in ( string $fieldName, ?string|array $value)

Creates an IN () comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The placeholder or the array of values to be used by IN() comparison.No automatic quoting/escaping is done.

Returns
string
notIn ( string $fieldName, ?string|array $value)

Creates a NOT IN () comparison expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

The placeholder or the array of values to be used by NOT IN() comparison.No automatic quoting/escaping is done.

Returns
string
inSet ( string $fieldName, string $value, bool $isColumn = false)

Returns a comparison that can find a value in a list field (CSV).

param $fieldName

The field name. Will be quoted according to database platform automatically.

param $value

Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.

param $isColumn

Set when the value to compare is a column on a table to activate casting, default: false

Returns
string
notInSet ( string $fieldName, string $value, bool $isColumn = false)

Returns a comparison that can find a value in a list field (CSV) but is negated.

param $fieldName

The field name. Will be quoted according to database platform automatically.

param $value

Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.

param $isColumn

Set when the value to compare is a column on a table to activate casting, default: false

Returns
string
bitAnd ( string $fieldName, int $value)

Creates a bitwise AND expression with the given arguments.

param $fieldName

The fieldname. Will be quoted according to database platform automatically.

param $value

Argument to be used in the bitwise AND operation

Returns
string

Remarks and warnings:

Examples:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php 
// use TYPO3\CMS\Core\Database\Connection;

// `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, Connection::PARAM_INT))

// `uid` >= 42
->gte('uid', $queryBuilder->createNamedParameter(42, Connection::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
    )
)
Copied!

See available parameter types.

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.

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
min ( string $fieldName, ?string $alias = NULL)

Creates a MIN expression for the given field/alias.

param $fieldName

the fieldName

param $alias

the alias, default: NULL

Returns
string
max ( string $fieldName, ?string $alias = NULL)

Creates a MAX expression for the given field/alias.

param $fieldName

the fieldName

param $alias

the alias, default: NULL

Returns
string
avg ( string $fieldName, ?string $alias = NULL)

Creates an AVG expression for the given field/alias.

param $fieldName

the fieldName

param $alias

the alias, default: NULL

Returns
string
sum ( string $fieldName, ?string $alias = NULL)

Creates a SUM expression for the given field/alias.

param $fieldName

the fieldName

param $alias

the alias, default: NULL

Returns
string
count ( string $fieldName, ?string $alias = NULL)

Creates a COUNT expression for the given field/alias.

param $fieldName

the fieldName

param $alias

the alias, default: NULL

Returns
string

Examples:

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php 
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use Doctrine\DBAL\Exception;
use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    /**
     * Calculate the average creation timestamp of all rows from tt_content
     * SELECT AVG(`crdate`) AS `averagecreation` FROM `tt_content`
     * @return array<mixed>
     * @throws Exception
     */
    public function findAverageCreationTime(): array
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $result = $queryBuilder
            ->addSelectLiteral(
                $queryBuilder->expr()->avg('crdate', 'averagecreation'),
            )
            ->from(self::TABLE_NAME)
            ->executeQuery()
            ->fetchAssociative();
        return $result;
    }

    /**
     * Distinct list of all existing endtime values from tt_content
     * SELECT `uid`, MAX(`endtime`) AS `maxendtime` FROM `tt_content` GROUP BY `endtime`
     * @return array<array<mixed>>
     * @throws Exception
     */
    public function findDistinctiveEndtimeValues(): array
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $result = $queryBuilder
            ->select('uid')
            ->addSelectLiteral(
                $queryBuilder->expr()->max('endtime', 'maxendtime'),
            )
            ->from('tt_content')
            ->groupBy('endtime')
            ->executeQuery()
            ->fetchAllAssociative()
        ;
        return $result;
    }
}
Copied!

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

Various expressions

ExpressionBuilder::as()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
as ( string $expression, string $asIdentifier = '')
param $expression

Value, identifier or expression which should be aliased

param $asIdentifier

Alias identifier, default: ''

Return description

Returns aliased expression

Returns
string

Creates a statement to append a field alias to a value, identifier or sub-expression.

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 = 'tx_myextension_table';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function demonstrateExpressionBuilderAs(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        $expressionBuilder = $queryBuilder->expr();

        // Alias the result of "1+1+1" as column "calculated_field" (containing "3")
        $queryBuilder->selectLiteral(
            $queryBuilder->quoteIdentifier('uid'),
            $expressionBuilder->as('(1 + 1 + 1)', 'calculated_field'),
        );

        // Alias a calculated sub-expression of concatenating "1", " " and "1" as
        // column "concatenated_value", containing "1 1".
        $queryBuilder->selectLiteral(
            $queryBuilder->quoteIdentifier('uid'),
            $expressionBuilder->as(
                $expressionBuilder->concat(
                    $expressionBuilder->literal('1'),
                    $expressionBuilder->literal(' '),
                    $expressionBuilder->literal('1'),
                ),
                'concatenated_value',
            ),
        );
    }
}
Copied!

ExpressionBuilder::concat()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
concat ( string ...$parts)
param $parts

the parts

Return description

Returns the concatenation expression compatible with the database connection platform

Returns
string

Can be used to concatenate values, row field values or expression results into a single string value.

The created expression is built on the proper platform-specific and preferred concatenation method, for example field1 || field2 || field3 || ... for SQLite and CONCAT(field1, field2, field3, ...) for other database vendors.

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php 
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyTableRepository
{
    private const TABLE_NAME = 'pages';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function demonstrateConcat(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        $expressionBuilder = $queryBuilder->expr();
        $result = $queryBuilder
            ->select('uid', 'pid', 'title', 'page_title_info')
            ->addSelectLiteral(
                $expressionBuilder->concat(
                    $queryBuilder->quoteIdentifier('title'),
                    $queryBuilder->quote(' - ['),
                    $queryBuilder->quoteIdentifier('uid'),
                    $queryBuilder->quote('|'),
                    $queryBuilder->quoteIdentifier('pid'),
                    $queryBuilder->quote(']'),
                ) . ' AS ' . $queryBuilder->quoteIdentifier('page_title_info'),
            )
            ->where(
                $expressionBuilder->eq(
                    'pid',
                    $queryBuilder->createNamedParameter(0, Connection::PARAM_INT),
                ),
            )
            ->executeQuery();
        while ($row = $result->fetchAssociative()) {
            // $row = [
            //  'uid' => 1,
            //  'pid' => 0,
            //  'title' => 'Site Root Page',
            //  'page_title_info' => 'Site Root Page - [1|0]',
            // ]
            // ...
        }
    }
}
Copied!

ExpressionBuilder::castInt()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
castInt ( string $value, string $asIdentifier = '')
param $value

Quoted value or expression result which should be cast to integer type

param $asIdentifier

Optionally add a field identifier alias (AS), default: ''

Return description

Returns the integer cast expression compatible with the connection database platform

Returns
string

Can be used to create an expression which converts a value, row field value or the result of an expression to signed integer type.

Uses the platform-specific preferred way for casting to dynamic length character type, which means CAST("value" AS INTEGER) for most database vendors except PostgreSQL. For PostgreSQL the "value"::INTEGER cast notation is used.

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 = 'my_table';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

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

        $queryBuilder
            ->select('uid')
            ->from('pages');

        // simple value (quoted) to be used as sub-expression
        $expression1 = $queryBuilder->expr()->castInt(
            $queryBuilder->quote('123'),
        );

        // simple value (quoted) to return as select field
        $queryBuilder->addSelectLiteral(
            $queryBuilder->expr()->castInt(
                $queryBuilder->quote('123'),
                'virtual_field',
            ),
        );

        // cast the contents of a specific field to integer
        $expression3 = $queryBuilder->expr()->castInt(
            $queryBuilder->quoteIdentifier('uid'),
        );

        // expression to be used as sub-expression
        $expression4 = $queryBuilder->expr()->castInt(
            $queryBuilder->expr()->castVarchar('(1 * 10)'),
        );

        // expression to return as select field
        $queryBuilder->addSelectLiteral(
            $queryBuilder->expr()->castInt(
                $queryBuilder->expr()->castVarchar('(1 * 10)'),
                'virtual_field',
            ),
        );
    }
}
Copied!

ExpressionBuilder::castVarchar()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
castVarchar ( string $value, int $length = 255, string $asIdentifier = '')
param $value

Unquoted value or expression, which should be cast

param $length

Dynamic varchar field length, default: 255

param $asIdentifier

Used to add a field identifier alias (AS) if non-empty string (optional), default: ''

Return description

Returns the cast expression compatible for the database platform

Returns
string

Can be used to create an expression which converts a value, row field value or the result of an expression to varchar type with dynamic length.

Uses the platform-specific preferred way for casting to dynamic length character type, which means CAST("value" AS VARCHAR(<LENGTH>)) or CAST("value" AS CHAR(<LENGTH>)) is used, except for PostgreSQL. For PostgreSQL the "value"::INTEGER cast notation is used.

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 = 'my_table';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

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

        $fieldVarcharCastExpression = $queryBuilder->expr()->castVarchar(
            $queryBuilder->quote('123'), // integer as string
            255,                         // convert to varchar(255) field - dynamic length
            'new_field_identifier',
        );

        $fieldExpressionCastExpression2 = $queryBuilder->expr()->castVarchar(
            '(100 + 200)',           // calculate a integer value
            100,                     // dynamic varchar(100) field
            'new_field_identifier',
        );
    }
}
Copied!

ExpressionBuilder::if()

New in version 13.3

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
if ( TYPO3\CMS\Core\Database\Query\Expression\CompositeExpression|Doctrine\DBAL\Query\Expression\CompositeExpression|Stringable|string $condition, Stringable|string $truePart, Stringable|string $falsePart, ?Stringable|string|null $as = NULL)
param $condition

the condition

param $truePart

the truePart

param $falsePart

the falsePart

param $as

the as, default: NULL

Returns
string

This method is used for "if-then-else" expressions. These are translated into IF or CASE statements depending on the database engine in use.

Example:

// use TYPO3\CMS\Core\Database\Connection;

$queryBuilder
    ->selectLiteral(
        $queryBuilder->expr()->if(
            $queryBuilder->expr()->eq(
                'hidden',
                $queryBuilder->createNamedParameter(0, Connection::PARAM_INT)
            ),
            $queryBuilder->quote('page-is-visible'),
            $queryBuilder->quote('page-is-not-visible'),
            'result_field_name'
        ),
    )
    ->from('pages');
Copied!

Result with MySQL/MariaDB:

SELECT
    (IF(`hidden` = 0, 'page-is-visible', 'page-is-not-visible')) AS `result_field_name`
    FROM `pages`
Copied!

ExpressionBuilder::left()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
left ( string|int $length, string $value, string $asIdentifier = '')
param $length

Integer value or expression providing the length as integer

param $value

Value, identifier or expression defining the value to extract from the left

param $asIdentifier

Provide AS identifier if not empty, default: ''

Return description

Return the expression to extract defined substring from the right side.

Returns
string

Extract $length characters of $value from the left side.

Creates a LEFT("value", number_of_chars) expression for all supported database vendors except SQLite, where substring("value", start[, number_of_chars]) is used to provide a compatible expression.

ExpressionBuilder::leftPad()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
leftPad ( string $value, string|int $length, string $paddingValue, string $asIdentifier = '')
param $value

Value, identifier or expression defining the value which should be left padded

param $length

Padded length, to either fill up with $paddingValue on the left side or crop to

param $paddingValue

Padding character used to fill up if characters are missing on the left side

param $asIdentifier

Provide AS identifier if not empty, default: ''

Return description

Returns database connection platform compatible left-pad expression.

Returns
string

Left-pad the value or sub-expression result with $paddingValue, to a total length of $length.

SQLite does not support LPAD("value", length, "paddingValue"), therefore a more complex compatible replacement expression construct is created.

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 demonstrateLeftPad(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        // Left-pad "123" with "0" to an amount of 10 times, resulting in "0000000123"
        $expression1 = $queryBuilder->expr()->leftPad(
            $queryBuilder->quote('123'),
            10,
            '0',
        );

        // Left-pad contents of the "uid" field with "0" to an amount of 10 times, a uid=1 would return "0000000001"
        $expression2 = $queryBuilder->expr()->leftPad(
            $queryBuilder->expr()->castVarchar($queryBuilder->quoteIdentifier('uid')),
            10,
            '0',
        );

        // Sub-expression to left-pad the concated string result ("1" + "2" + "3") up to 10 times with 0, resulting in "0000000123".
        $expression3 = $queryBuilder->expr()->leftPad(
            $queryBuilder->expr()->concat(
                $queryBuilder->quote('1'),
                $queryBuilder->quote('2'),
                $queryBuilder->quote('3'),
            ),
            10,
            '0',
        );

        // Left-pad the result of sub-expression casting "1123" to a string,
        // resulting in "0000001123".
        $expression4 = $queryBuilder->expr()->leftPad(
            $queryBuilder->expr()->castVarchar('( 1123 )'),
            10,
            '0',
        );

        // Left-pad the result of sub-expression casting "1123" to a string,
        // resulting in "0000001123" being assigned to "virtual_field"
        $expression5 = $queryBuilder->expr()->leftPad(
            $queryBuilder->expr()->castVarchar('( 1123 )'),
            10,
            '0',
            'virtual_field',
        );
    }
}
Copied!

ExpressionBuilder::length()

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
length ( string $fieldName, ?string $alias = NULL)

Creates a LENGTH expression for the given field/alias.

param $fieldName

the fieldName

param $alias

the alias, default: NULL

Returns
string

The length() string function can be used to return the length of a string in bytes.

EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php 
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function findFieldLongerThenZero(string $fieldName): QueryBuilder
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $queryBuilder->expr()->comparison(
            $queryBuilder->expr()->length($fieldName),
            ExpressionBuilder::GT,
            $queryBuilder->createNamedParameter(0, Connection::PARAM_INT),
        );
        return $queryBuilder;
    }
}
Copied!

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

ExpressionBuilder::repeat()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
repeat ( string|int $numberOfRepeats, string $value, string $asIdentifier = '')
param $numberOfRepeats

Statement or value defining how often the $value should be repeated. Proper quoting must be ensured.

param $value

Value which should be repeated. Proper quoting must be ensured

param $asIdentifier

Provide AS identifier if not empty, default: ''

Return description

Returns the platform compatible statement to create the x-times repeated value

Returns
string

Create a statement to generate a value repeating defined $value for $numberOfRepeats times. This method can be used to provide the repeat number as a sub-expression or calculation.

REPEAT("value", numberOfRepeats) is used to build this expression for all database vendors except SQLite for which the compatible replacement construct expression REPLACE(PRINTF('%.' || <valueOrStatement> || 'c', '/'),'/', <repeatValue>) is used, based on REPLACE() and the built-in printf().

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 = 'pages';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function demonstrateRepeat(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        // Repeats "." 10 times, resulting in ".........."
        $expression1 = $queryBuilder->expr()->repeat(
            10,
            $queryBuilder->quote('.'),
        );

        // Repeats "0" 20 times and allows to access the field as "aliased_field" in query / result
        $expression2 = $queryBuilder->expr()->repeat(
            20,
            $queryBuilder->quote('0'),
            $queryBuilder->quoteIdentifier('aliased_field'),
        );

        // Repeat contents of field "table_field" 20 times and makes it available as "aliased_field"
        $expression3 = $queryBuilder->expr()->repeat(
            20,
            $queryBuilder->quoteIdentifier('table_field'),
            $queryBuilder->quoteIdentifier('aliased_field'),
        );

        // Repeate database field "table_field" the number of times that is cast to integer from the field "repeat_count_field" and make it available as "aliased_field"
        $expression4 = $queryBuilder->expr()->repeat(
            $queryBuilder->expr()->castInt(
                $queryBuilder->quoteIdentifier('repeat_count_field'),
            ),
            $queryBuilder->quoteIdentifier('table_field'),
            $queryBuilder->quoteIdentifier('aliased_field'),
        );

        // Repeats the character "." as many times as the result of the expression "7+3" (10 times)
        $expression5 = $queryBuilder->expr()->repeat(
            '(7 + 3)',
            $queryBuilder->quote('.'),
        );

        // Repeat 10 times the result of a concatenation expression (".") and make it available as "virtual_field_name"
        $expression6 = $queryBuilder->expr()->repeat(
            '(7 + 3)',
            $queryBuilder->expr()->concat(
                $queryBuilder->quote(''),
                $queryBuilder->quote('.'),
                $queryBuilder->quote(''),
            ),
            'virtual_field_name',
        );
    }
}
Copied!

ExpressionBuilder::right()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
right ( string|int $length, string $value, string $asIdentifier = '')
param $length

Integer value or expression providing the length as integer

param $value

Value, identifier or expression defining the value to extract from the left

param $asIdentifier

Provide AS identifier if not empty, default: ''

Return description

Return the expression to extract defined substring from the right side

Returns
string

Extract $length characters of $value from the right side.

Creates a RIGHT("value", length) expression for all supported database vendors except SQLite, where substring("value", start_of_string[, length]) is used to provide a compatible expression.

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 = 'pages';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function demonstrateRight(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        // Returns the right-side 6 characters of "some-string" (result: "string")
        $expression1 = $queryBuilder->expr()->right(
            6,
            $queryBuilder->quote('some-string'),
        );

        // Returns the right-side calculated 7 characters of "some-string" (result: "-string")
        $expression2 = $queryBuilder->expr()->right(
            '(3+4)',
            $queryBuilder->quote('some-string'),
        );

        // Returns a sub-expression (casting "8" as integer) to return "g-string"
        $expression3 = $queryBuilder->expr()->right(
            $queryBuilder->expr()->castInt('(8)'),
            $queryBuilder->quote('some-very-log-string'),
        );

        // Return the right-side 23 characters from column "table_field_name"
        $expression4 = $queryBuilder->expr()->right(
            23,
            $queryBuilder->quoteIdentifier('table_field_name'),
        );
    }
}
Copied!

ExpressionBuilder::rightPad()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
rightPad ( string $value, string|int $length, string $paddingValue, string $asIdentifier = '')
param $value

Value, identifier or expression defining the value which should be right padded

param $length

Value, identifier or expression defining the padding length to fill up or crop

param $paddingValue

Padding character used to fill up if characters are missing on the right side

param $asIdentifier

Provide AS identifier if not empty, default: ''

Return description

Returns database connection platform compatible right-pad expression

Returns
string

Right-pad the value or sub-expression result with $paddingValue, to a total length of $length.

SQLite does not support RPAD("value", length, "paddingValue"), therefore a more complex compatible replacement expression construct is created.

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 = 'pages';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function demonstrateRightPad(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        // Right-pad the string "123" up to ten times with "0", resulting in "1230000000"
        $expression1 = $queryBuilder->expr()->rightPad(
            $queryBuilder->quote('123'),
            10,
            '0',
        );

        // Right-pad the cnotents of field "uid" up to ten times with 0, for uid=1 results in "1000000000".
        $expression2 = $queryBuilder->expr()->rightPad(
            $queryBuilder->expr()->castVarchar($queryBuilder->quoteIdentifier('uid')),
            10,
            '0',
        );

        // Right-pad the results of concatenating "1" + "2" + "3" ("123") up to 10 times with 0, resulting in "1230000000"
        $expression3 = $queryBuilder->expr()->rightPad(
            $queryBuilder->expr()->concat(
                $queryBuilder->quote('1'),
                $queryBuilder->quote('2'),
                $queryBuilder->quote('3'),
            ),
            10,
            '0',
        );

        // Left-pad the result of sub-expression casting "1123" to a string,
        // resulting in "1123000000""
        $expression4 = $queryBuilder->expr()->rightPad(
            $queryBuilder->expr()->castVarchar('( 1123 )'),
            10,
            '0',
        );

        // Right-pad the string "123" up to 10 times with "0" and make the result ("1230000000") available as "virtual_field"
        $expression5 = $queryBuilder->expr()->rightPad(
            $queryBuilder->quote('123'),
            10,
            '0',
            'virtual_field',
        );
    }
}
Copied!

ExpressionBuilder::space()

New in version 13.1

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
space ( string|int $numberOfSpaces, string $asIdentifier = '')
param $numberOfSpaces

Statement or value defining how often a space should be repeated. Proper quoting must be ensured.

param $asIdentifier

Provide AS identifier if not empty, default: ''

Return description

Returns the platform compatible statement to create the x-times repeated space(s).

Returns
string

Create a statement containing $numberOfSpaces space characters.

The SPACE(numberOfSpaces) expression is used for MariaDB and MySQL and ExpressionBuilder::repeat() expression as fallback for PostgreSQL and SQLite.

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 = 'pages';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function demonstrateSpace(): void
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable(self::TABLE_NAME);
        // Returns "          " (10 space characters)
        $expression1 = $queryBuilder->expr()->space(
            '10',
        );

        // Returns "          " (10 space characters) and makes available as "aliased_field"
        $expression2 = $queryBuilder->expr()->space(
            '20',
            $queryBuilder->quoteIdentifier('aliased_field'),
        );

        // Return amount of space characters based on calculation (10 spaces)
        $expression3 = $queryBuilder->expr()->space(
            '(7+2+1)',
        );

        // Return amount of space characters based on a fixed value (210 spaces) and make available as "aliased_field"
        $expression3 = $queryBuilder->expr()->space(
            '(210)',
            $queryBuilder->quoteIdentifier('aliased_field'),
        );

        // Return a space X times, where X is the contents of the field table_repeat_number_field
        $expression5 = $queryBuilder->expr()->space(
            $queryBuilder->expr()->castInt(
                $queryBuilder->quoteIdentifier('table_repeat_number_field'),
            ),
        );

        $expression6 = $queryBuilder->expr()->space(
            $queryBuilder->expr()->castInt(
                $queryBuilder->quoteIdentifier('table_repeat_number_field'),
            ),
            $queryBuilder->quoteIdentifier('aliased_field'),
        );
    }
}
Copied!

ExpressionBuilder::trim()

class ExpressionBuilder
Fully qualified name
\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
trim ( string $fieldName, \Doctrine\DBAL\Platforms\TrimMode $position = \Doctrine\DBAL\Platforms\TrimMode::UNSPECIFIED, ?string $char = NULL)

Creates a TRIM expression for the given field.

param $fieldName

Field name to build expression for

param $position

Either constant out of LEADING, TRAILING, BOTH, default: DoctrineDBALPlatformsTrimMode::UNSPECIFIED

param $char

Character to be trimmed (defaults to space), default: NULL

Returns
string

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 
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function findFieldThatIsEmptyWhenTrimmed(string $fieldName): QueryBuilder
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
        $queryBuilder->expr()->comparison(
            $queryBuilder->expr()->trim($fieldName),
            ExpressionBuilder::EQ,
            $queryBuilder->createNamedParameter('', Connection::PARAM_STR),
        );
        return $queryBuilder;
    }
}
Copied!

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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

  • trim('fieldName') results in TRIM("tableName"."fieldName")
  • trim('fieldName', TrimMode::LEADING, 'x') results in TRIM(LEADING "x" FROM "tableName"."fieldName")
  • trim('fieldName', TrimMode::TRAILING, 'x') results in TRIM(TRAILING "x" FROM "tableName"."fieldName")
  • trim('fieldName', TrimMode::BOTH, 'x') results in TRIM(BOTH "x" FROM "tableName"."fieldName")